Microsoft Office Tutorials and References

In Depth Information

**Advanced Text Formulas**

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

This formula returns an error if the cell contains only one word. The formula below 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

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 handle the special

cases.