Microsoft Office Tutorials and References

In Depth Information

Splitting text strings without using formulas

In many cases, you can eliminate the use of formulas and use Excel’s Data⇒Data Tools⇒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

converting 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).

Another string-splitting option is to use the Flash Fill feature, introduced in Excel 2013.

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,” “,””))))),””)