Extract the First & Last Name from a String in Excel

In a previous post, we saw how to concatenate two or more cells into one. There may be times, however, when you need to extract data from a string. For instance, the customer's first and last name may be located in the same cell but you need them to each be in their own cell. As long as there is a space, comma or other delimiter between the words, then you can easily extract the first and last names from the string.

To extract the first name from a string in which the words are separated by a space (such as Mary Nolan), use the following formula:

=LEFT(A1,FIND(" ", A1)-1)

Extracting the last word of a string is a little more complicated, but still doable.

=RIGHT(A2,LEN(A2)-FIND(" ",A2,1))

If your first and last names are separated with a comma with no space between the words (such as Mary,Nolan) replace " " with ",". That is to say, we tell Excel to search for a comma, rather than simply a space. So to extract the first name from a string in which the words are separated by a comma, use the following formula:

=LEFT(A1,FIND(",",A1)-1)

If the last name comes first (Nolan, Mary), use the formula to extract the last word from a string:

=RIGHT(A2,LEN(A2)-FIND(",",A2,1))

If the words are separated by a comma and a space (such as Mary, Nolan), then add a space after the comma surrounded by quotes:

=LEFT(A1,FIND(", ",A1)-1)

Looking for more Excel tips? Check out our new book Excel 2016: Up To Speed available as an ebook on Amazon, Kobo, and Apple iBooks