Chapter 11: Using Formulas to Manipulate Text
11
FIGURE 11.5
Using the LEFT, RIGHT, and MID functions
The LEFT function allows you to extract a given number of characters from the left of a
given text string. The LEFT function requires two arguments: the text you are evaluating
and the number of characters you need to be extracted from the left of the text string. In
this example, we are extracting the left five characters from the value in cell A4:
=LEFT(A4,5)The RIGHT function allows you to extract a given number of characters from the right of
a given text string. The RIGHT function requires two arguments: the text string you are
evaluating and the number of characters you need to be extracted from the right of the
text string. In this example, we are extracting the right eight characters from the value in
cell A9:
=RIGHT(A9,8)The MID function allows you to extract a given number of characters from the middle of
a given text string. The MID function requires three arguments: the text string you are
evaluating, the character position in the text string from where to start the extraction,
and the number of characters you need to be extracted. In this example, we are starting at
the fourth character in our text string and extracting one character:
=MID(A14,4,1)Finding a particular character in a text string
Excel’s LEFT, RIGHT, and MID functions work great for extracting text, but only if you
know the exact position of the characters you are targeting. What can you do when you
don’t know exactly where to start the extraction? For example, if you had the following list
of product codes, how would you go about extracting all of the text after the hyphens?