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




Custom Search