Use Text Functions
When data is copied from another source such as a Website
you may need to manipulate or transform it in some way. The text functions
mentioned in this unit can are designed to transform data. For example the UPPER function transforms
text to upper case. The LEFT and RIGHT function truncates text to a specific
number of characters to the left or right. Other functions such as FIND are
used in combination with other text functions to locate the position of spaces
or other characters within text. Other functions such as TRIM are used to clean
out spaces from the beginning or end of text.
Often you will want to replace the original data with the transformed data. To do that you will need to create the transformed data containing the text function in a temporary column. The new data containing the function or functions can then be copied but then pasted back as Values onto the original data.
LEFT
The LEFT function displays a specific number of characters
from the left hand side of a piece of text.
Syntax
=LEFT(Text,NumberOfCharacters)
Suppose you import a list of athletes from the 2012 Olympic
Website and find the Last name is in capitals and before the first name. You
would rather display the names as First name then Last name in Proper case but
there are 107 runners so you don’t feel like retyping the names.
Use of the LEFT function wouldn’t help because the Last
names are all different lengths. You could display the Last name initials by
typing in B3:
=LEFT(A3,1)
Copying the function down will display all athlete initials.
But to display the Last names you need first to find the position of the space
and use that to determine the length of Number of characters to display.
In Cell B3 you would type:
=LEFT(A3,FIND(" ",A3)-1)
This formula uses the text function FIND and will work for
all the athletes with a single Last names.
FIND
The FIND function looks for a specific character or
characters in the text within a cell and returns its character position
counting from the left.
returns the position 1if the text happens to contain 2 spaces, (3 names rather than 2) otherwise it returns #VALUE.
For example for GELANA tiki
FIND(" ",A3) returns 7
Subtracting 1 results in the number of charaters for her
Last name.
You can optionally start part way through the text by typing
a startnumber. That might be useful if there is more than one space in a full
name.
Syntax
=FIND(FindText,WithinText,[startnumber]))
So combing the LEFT and FIND functions results in a list of
Last names only. There are two exceptions, PETROVA ARKHIPOVA Tatyana and DA
SILVA Adriana Aparecida because there are effectively two Last names without a
hyphens between them. They will have to
be edited manually but 105 names are separated automatically with these text
functions.
SEARCH
The SEARCH function is very similar to FIND..The main
difference is that SEARCH is not case sensitive and it allows ‘wildcards’ to be
used. A ? stands for single
character and a * stands for any
number of characters.
For example
=SEARCH("* * ",A3)
returns the position 1if the text happens to contain 2
spaces, (3 names rather than 2) otherwise it returns #VALUE.
PROPER
This function capitalizes the first character of each work
of the text within a cell.
Syntax
=PROPER(TextToConvert)
To convert text to Proper case simply type =PROPER(A3) in a
blank column and copy down.
UPPER and LOWER
These text functions convert all text in cell to upper or lower case.
Syntax
=UPPER(Text)
=LOWER(Text)
They may be useful when there is a mixture of case. They can
also be wrapped around other functions, for example,
=PROPER(LEFT(A3,FIND(" ",A3)-1))
LEN
This function counts the number of characters in a piece of
text including spaces and numbers.
Syntax
=LEN(Text)
At first there may not seem to be a business need for
counting thre number of characters in piece of text. But it can be useful for transforming data
when used in combination with other text functions.
In the example below, suppose you wish to separate the
country from the athlete name and display it in column C. Unfortunately there
is no space or other delimiter after the country name to help you. This is
because the data came originally from a flag on the Website next to each
athlete name. So you the SEARCH function or even Excel’s powerful Text to
Columns feature on the Data tab will not help you.
But if you have the athlete name already it should be possible to use a combination of the LEN and LEFT function to subtract extract the country name.
0 Comments