Microsoft Office Tutorials and References

In Depth Information

**Extracting Words from a String**

Extracting Words from a String

The formulas in this tip are useful for extracting words from text contained in a cell. For example,

you can use a formula to extract the first word in a sentence.

Extracting the first word of a string

To extract the first word of a string, a formula must locate the position of the first space

character and then use this information as an argument for the LEFT function. The following formula

does that:

=LEFT(A1,FIND(“ “,A1)-1)

This formula returns all the text before the first space in cell A1. However, the formula has a slight

problem: It returns an error if the text in cell A1 contains no space characters, because it consists

of a single word. A slightly more complex formula solves the problem by using the new IFERROR

function to display the entire cell contents if an error occurs:

=IFERROR(LEFT(A1,FIND(“ “,A1)-1),A1)

If you need the formula to be compatible with earlier versions of Excel, you can’t use IFERROR.

Therefore, use an IF function and an ISERR function to check for the error:

=IF(ISERR(FIND(“ “,A1)),A1,LEFT(A1,FIND(“ “,A1)-1))

Extracting the last word of a string

Extracting the last word of a string is more complicated because the FIND function works only

from left to right. Therefore, the problem rests with locating the
last
space character. The

following formula, however, solves this problem. The formula returns the last word of a string (all the

text following the last space character):

=RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN

(SUBSTITUTE(A1,” “,””)))))

This formula, however, has the same problem as the first formula in the preceding section: It fails

if the string doesn’t contain at least one space character. The solution is to use the IFERROR

function and return the entire contents of cell A1 if an error occurs: