Microsoft Office Tutorials and References
In Depth Information
Using the substring text functions
The RIGHT and LEFT functions
The RIGHT function returns the rightmost series of characters from a specified string; the
LEFT function returns the leftmost series of characters. These functions take the same
arguments: ( text, num_chars ). The num_chars argument indicates the number of characters to
extract from the text argument.
Practical text manipulation
Excel excels at text manipulation. If you deal with a lot of mailing lists, for example,
here is a trick you might find useful, and it serves as a good example as well.
Suppose you import a database of addresses in which the first and last names are stored
together in the same cell. This example shows you a formula you can use to parse them
into separate columns:
The formulas in columns C and D use the SEARCH function to locate the space
character. The function assigns it a number based on its position in the cell. Inside the RIGHT
function shown in this figure, the number is subtracted from the total number of
characters in the cell, provided by the LEN function. This returns everything to the right
of the space. The formula you can’t see in cell C2 doesn’t require the LEN function:
=LEFT(A2,SEARCH(" ",A2)). You might want to use the TRIM function on column A first
to be sure there are no extra spaces. For a related text-manipulation trick, see “Practical
concatenation” in Chapter 12. And for an even better trick that is new in Excel 2013,
see “Automatic parsing and concatenation using Flash Fill” in Chapter 8, “Worksheet
You’ll find the Text.xlsx file with the other examples on the companion website.
These functions count blank spaces in the text argument as characters; if text contains
leading or trailing blank characters, you might want to use a TRIM function within the RIGHT or
LEFT function to ensure the expected result. For example, suppose you type This is a test
in cell A1 on your worksheet. The formula =RIGHT(A1, 4) returns the word test .