Menu
Cart 0

Extract the First & Last Name from a String in Excel

Posted by Roger Hyttinen on

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 First and Last Name in Excel with no space


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

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

 

Extract Last Name from a String in Excel

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)

 

Extract First and Last Name from an Excel String with comma

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


Share this post



← Older Post Newer Post →


Leave a comment

Please note, comments must be approved before they are published.