Microsoft Office Tutorials and References

In Depth Information

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

avoid the error value:

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

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

For compatibility with versions prior to Excel 2007, use this formula:

=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
2013 Operating Budget,
the formula then returns
Operating Budget.

This formula returns an error if the cell contains only one word. The following formula solves this problem and

returns an empty string if the cell does not contain multiple words:

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

For compatibility with versions prior to Excel 2007, use this formula:

=IF(ISERR(FIND(“ “,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 initially think because not every name in the column has a middle name or

middle initial. However, you can still do it.

The task becomes a lot more complicated if the list contains names with titles (such as

Mrs. or Dr.) or names followed by additional details (such as Jr. or III). In fact, the follow-

ing formulas will not handle these complex cases. However, they still give you a signi-

ficant head start if you're willing to do a bit of manual editing to handle the special

cases.