Extracting first names, middle names, and last names

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