Microsoft Office Tutorials and References

In Depth Information

**Advanced Text Formulas**

The formulas that follow all assume that the name appears in cell A1.

You can easily construct a formula to return the first name:

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

Returning the middle name or initial is much more complicated because not all names have a

middle initial. This formula returns the middle name or initial (if it exists); otherwise, it returns

nothing:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))>1,MID(A1,FIND(“ “,A1)+1,FIND(“ “,A1,FIND(“ “,

A1)+1)-(FIND(“ “,A1)+1)),””)

Finally, this formula returns the last name:

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

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

Splitting text strings without using formulas

In many cases, you can eliminate the use of formulas and use Excel’s Data

Convert Text

to Columns command to parse strings into their component parts. Selecting this command displays

Excel’s Convert Text to Columns Wizard (see the accompanying figure), which consists of a series of

dialog boxes that walk you through the steps to convert a single column of data into multiple

columns. Generally, you’ll want to select the Delimited option (in Step 1) and use Space as the delimiter

(in Step 2).

➜

Data Tools

➜