Excel 2010 Bible

Chapter 11: Creating Formulas That Manipulate Text

245

=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 does not contain at least one space character. The following modified formula uses the

IFERROR function to test for an error (that is, no spaces). If the first argument returns an error, the

formula returns the complete contents of cell A1:

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

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

Following is a modification that doesn’t use the IFERROR function. This formula works for all ver-

sions of Excel:

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

“,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))))

Extracting all but the first word of a string ..............................................................

The following formula returns the contents of cell A1, except for the first word:

=RIGHT(A1,LEN(A1)-FIND(“ “,A1,1))

If cell A1 contains 2010 Operating Budget, the formula returns Operating Budget.

The following formula, which uses the IFERROR function, returns the entire contents of cell A1 if

the cell doesn’t have a space character:

=IFERROR(RIGHT(A1,LEN(A1)-FIND(“ “,A1,1)),A1)

A modification that works in all versions of Excel is

=IF(ISERR(FIND(“ “,A1)),A1,RIGHT(A1,LEN(A1)-FIND(“ “,A1,1)))

Extracting first names, middle names, and last names .............................................

Suppose you have a list consisting of people’s names in a single column. You have to separate these

names into three columns: one for the first name, one for the middle name or initial, and one for

the last name. This task is more complicated than you may think because it must handle the situa-

tion for a missing middle initial. However, you can still do it.

Note
The task becomes a lot more complicated if the list contains names with titles (such as Mr. or Dr.) or names
followed by additional details (such as Jr. or III). In fact, the following formulas will not handle these complex
cases. However, they still give you a significant head start if you’re willing to do a bit of manual editing to han-
dle special cases. For a way to remove these titles, see the next section, “Removing titles from names.” n