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
 
Search JabSto ::




Custom Search