Effective Data Analysis using Text

Functions
Dealing with Text Data
 Objective
• Cleaning the text
• Text Functions
 For cleaning noise from Text
• TRIM
• CLEAN
• SUBSTITUTE
 TRIM and CLEAN Functions
The TRIM Function
• The function TRIM(text) removes all spaces
from a text string except for single spaces
between words.

Clean Function
• The function clean(text) removes all not
printable characters from a specified cell
 CLEAN()
• Used to remove noise characters from the text.
• The character may be printable or non printable
• 36 characters can be removed from text using
clean formula.
• Out of 36 characters 26 are printable and 7 are
non printable characters.
• Characters with ASCII Code 1 ‐31, 129, 141, 143,
144, 157 are treated as noise and may be cleaned
using CLEAN Formula.
Non Printable Characters
Unicode
Character Description
2 Start of the Text
10 Line feed
32 Space
127 Delete
129 High Octet Preset
141 Reverse Line Feed
143 Single Shift Three
144 Device Control String
157 Operating System
160 Non Breaking Characters
 Clean()
• However 32, 127 and 160 can not be removed
using Clean Formula
• We need the substitute formula for the
handling these three characters
Example
 Text / String / Character
• The following three terms all used to refer to
"text" data. All three terms mean the same
thing.

– text data
– string data
– character data

9
 Text data
• Text data is used to store general purpose text
(e.g. names, places, descriptions, etc)

• You can't do "math" with text values

(obviously)

10
 Text isn't part of numerical calculations
(obviously)
Formula to formula view (press Cntrl-`)
add up all
numbers in
column C

(Same
Spreadsheet)

Text data in
C1 is not values view (press Cntrl-`)
included in
the Sum

11
Text Functions

12
 Text Functions
• Many functions are used to manipulate text
values.
• The following are only some of them
right( )
left( )
mid( )
concatenate( )
lower( )
upper( )
len( )

13
RIGHT, LEFT and MID functions

14
 RIGHT function
• The RIGHT function is used to isolate a specific
number of “characters” from the right hand
side of a text value.
RIGHT ( text, [numCharacters])

15
 LEFT
• The LEFT function is the same as the RIGHT
function, but it returns characters from the
LEFT side of the value.
• LEFT ( text, [numCharacters])

16
 numCharacters is optional
• The <numCharacters> parameter in the both
RIGHT and LEFT function is optional. If you don’t
specify it the default is 1 (one).

17
 MID ( text, startPosition , numCharacters)

• MID is used to get values from the middle of

some text.

• MID takes 3 parameters:

– The original text
– The position to start taking the new value from
– The number of characters to take for the new
value

18
 Example: MID ( text, startPosition ,
numCharacters)
• This example extracts the second through the
fourth characters from the original text value:

19
 Concatenation
( & ) and
CONCATENATE function
Textjoin

20
 Concatenation (&)
• Use & to combine (or concatenate) two different text values

Formula View

Values View

Notice that there is no

space between the two
values 21
 Concatenate many values
• You may concatenate many values together
Formula View

Values View

22
Concatenation with "literal" values
• You can also concatenate "literal" values.
• You must include the literal values inside quotes
• For example to display spaces in the "full name"
in the previous example you could use the
following formula. Each space that you want to
display must be included in quotes.

=A2&" "&B2&" "&C2

(Don't forget any of the &'s )
• See next slide ...
23
 Concatenating spaces ‐ Example
• You can concatenate spaces into a formula
Formula View

Values View values

contain
spaces

24
 LEFT( ) with & in same formula
• You can combine the results of different function
calls with concatenation.
Formula View

Values View

25
 Putting it all together
• In this example we concatenate periods into the
initials.
Formula View

Values View

The initials now contain

periods
26
 CONCATENATE Function
• You can use the CONCATENATE function instead
of the ampersand (&).
• The following formulas are equivalent:

=A1&B1&C1

=CONCATENATE(A1,B1,C1)

• The CONCATENATE function can take as many

parameters as you like.
27
 TEXTJOIN
• It concatenates the list or range of text string
using a delimiter.

textjoin(delimiter,ignore_empty, text1, text2,….)

Ignore_empty is TRUE for ignoring empty cells

Ignore_empty is FALSE for not ignoring empty
cells
More Text Functions:
LOWER
UPPER
PROPER

29
LOWER ( text), UPPER ( text) AND PROPER(text)

• LOWER converts text to lower case.

• UPPER converts text to upper case.
• PROPER Converts all characters in a supplied
text string to proper case i.e first character of
each word.

30
 LEN ( textValue )
• LEN returns a numeric value equal to the
number of character in a text value (i.e. the
“length” of the text value).
• Spaces ARE included in the length.
• Example

Formula View

Values View 31
 TRIM and CLEAN Functions
The TRIM Function
• The function TRIM(text) removes all spaces
from a text string except for single spaces
between words.

Clean Function
• The function clean(text) removes all not
printable characters from a specified cell
 Non Printable Characters
Unicode characterDesc
141 Reverse Line Feed
143 Single‐Shift Three
144 Device Control Str
157 Operating System
127 Delete

160 Non Breaking Char
10 text wrapping
129 High Octet Preset
Example
The FIND and SEARCH Functions
• The function FIND(text to find,actual text,k) returns
the location at or after character k of the first
character of text to find in the actual text. FIND is
case sensitive. SEARCH has the same syntax as FIND,
but it is not case sensitive.
 REPT Function
The REPT function allows you to repeat a text string a desired
number of times. The syntax is REPT(text,number of times). For
example REPT("|",3) will produce the output |||.
The REPLACE Function
• The function REPLACE(old text,k,m,new text)
begins at character k of old text and replaces
the next m characters with new text.

The SUBSTITUTE Function

• The function
Substitute(text,old text,k,m,new
text,[instance_num])
Replaces old text with new text in a text string. It
is case sensitive.
Example
The VALUE Function
• The function VALUE(text) converts a text string
that represents a number to a number.

T
• Function T returns value of referenced cell if
the referenced cell contains text else returns
empty cell
Example
• CHAR Function
• The function CHAR(number) will yield (for a
number between 1 and 255) the ASCII
character with that number.
• CODE Function
• Returns the numeric code for the first
character of a supplied string
 TEXT
• Uses to convert the format of atext data as per
user specified format
 TextJoin
• The TEXTJOIN function combines the text from
multiple ranges and/or strings, and includes a
delimiter you specify between each text value
that will be combined. If the delimiter is an
empty text string, this function will effectively
concatenate the ranges.
 Textjoin Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

argument Description
delimiter A text string, either empty, or one or more
(required) characters enclosed by double quotes, or a
reference to a valid text string. If a number is
supplied, it will be treated as text.

ignore_empty If TRUE, ignores empty cells.

(required)
text1 Text item to be joined. A text string, or array of
(required) strings, such as a range of cells.

[text2, ...] Additional text items to be joined. There can be a

(optional) maximum of 252 text arguments for the text
items, including text1. Each can be a text string,
or array of strings, such as a range of cells.
 Example
• For example, =TEXTJOIN(" ",TRUE, "The",
"sun", "will", "come", "up", "tomorrow.") will
return The sun will come up tomorrow.
• Remarks
 Flash fill
• Flash Fill automatically fills your data when it
senses a pattern. For example, you can use
Flash Fill to separate first and last names from
a single column, or combine first and last
names from two different columns.

• Shortcut Key : CTRL + E

 Business Problem
• You are working as business analyst with a
FMCG company. You exported the data from
the inhouse built ERP system information
about the customer in a text file.
• Importing the text file into a excel sheet and
clean it for performing few analysis.
 Questions
• In column D we have product name and quantity
together. Separate them in two columns so that we
find total quantity sold of all the different products.
• In next column display the quantity by creating a
horizontal bars.
• The first name and last name are given in column A and
B. In new column write the full name of customers with
only first character of first name as capital.
• As a manager of the store you need to find total
earning from the customer. If we try to sum all the data
of revenue column it gives 0 as answer. Modify the
sheet to get right answer.
 Questions
• While importing data from a company website
some unwanted white spaces had come in the
data. We need to remove all such white spaces, if
any.
• Enter your full name in a cell, how many
characters including spaces are there in your
name.
• Extract the first three characters of your name.
• Extract the last three characters of your name.
 Questions
• Extract the three characters from fifth
character onward.
• Assume your email address is
firstname.lastname@gmail.com. Modify your
email address to
firstname_lastname@gmail.com
 Practice Questions
• First name and last name of candidate is given in two cells. Write the full name of
the candidate in one cell.
• The name of candidate is in small letters we need to print the name in I‐Card in all
capital letters.
• Company is planning to move their employee email server from gmail to Hotmail.
Change all the email ids from gmail.com to Hotmail.com.
• Apart from blank cells few non printable characters has also appeared in data. You
need to remove them.
• How many characters without space is there in name “ ATAL BIHARI VAJPAYEE”
• How will you convert a number into text.
• Write a paragraph about our city in a cell. Convert all characters in uppercase.
• Convert all the characters in lower case with the first character of sentence as
upper case.
• What is the difference between Substitute and replace formula.
• How many white spaces are there in the text
• “ I am studying at IIM lucknow”