Separate Full Name into First & Last Name
- 14 Comment
Here are two easy formulas that will separate a cell that contains a full name (first and last name format) into 2 cells – one containing the first name and the other containing the last name. To do this we are going to make use of the left, right, len & find formulas.
Extract First Name from Full Name
To extract the first name from cell A1, which contains the full name, we use the formula as follows:
=LEFT(A1,FIND(” “,A1)-1)
The way this work is the find formula returns the number of characters where it finds the first space – we then subtract one from that formula because we want the location of the last non-space character. The Left formula then takes the first characters equal to the number returned by FIND()-1.
Extract Last Name from Full Name
To get the last name from the full name, we are going to piggyback off the original formula from above. Using the following formula will extract the last name from full name:
=RIGHT(A1,LEN(A1)-FIND(” “,A1))
Let’s take a look at this formula. The first thing that is done is we find the total length of the cell contents of A1. Next, the character count of the location of the first space is subtracted from the total length of A1. This should leave us with the length of the last name. The final step is to take the Right formula and select the number of characters in the length of the last name of cell A1.
Maybe taking a look at the examples will make it clearer.
Example
Cell A1 contains the full name “John Smith”
First Name Formula: =LEFT(A1,FIND(” “,A1)-1) – this will return “John”.
Last Name Formula: =RIGHT(A1,LEN(A1)-FIND(” “,A1)) – this will return “Smith”.
Easy right? Be careful though, these formulas will only work in the format of Full Name = First Name + Space + Last Name. Lets take a look at what happens if we have the format – Full Name = First Name + Space + Middle Name + Space + Last Name.
Full Name = “John Daniel Smith”
First Name Formula: =LEFT(A1,FIND(” “,A1)-1) – this will return “John”.
Last Name Formula: =RIGHT(A1,LEN(A1)-FIND(” “,A1)) – this will return “Daniel Smith”.
As you can see, the first name formula works correctly. However, the last name formula will return both the middle and last name. We will return to this in a later post to explore how to get all three names out of the Full Name in that format.
Random Posts
14 Comments on this post
Trackbacks
-
Rod Merrill said:
There’s a much easier method for splitting names in Excel 2007 by using the Text to Columns feature. Go to http://www.brainstorminc.com/cbt/microsoft/help.php?file=excel15 to watch what I believe is an excellent free instructional video.
January 25th, 2009 at 9:17 am -
john said:
Thanks Rod for the suggestion. You are correct that the text to columns feature can be used in many circumstances to quickly break out cells into multiple cells.
Sometimes however you may need a little more flexibility and the formula might be the way to go. Thanks for visiting Excel Hints and suggesting the video.
January 25th, 2009 at 12:40 pm -
john said:
I also believe the Text to Columns feature is available in previous versions of Excel as well. Thanks again for the suggestion Rod.
I might have to write a post about that soon showing how that feature works – its very similar to import a delimited file.
January 25th, 2009 at 12:44 pm -
Paul said:
Hi John,
This has been helpful. Thank you.
Have you posted getting all three names out as you mentioned? I am working with a client whose name data is in multiple formats.Example name1 – Larry W & Sharon R Smith
Example name2 – Larry Smith
Example name3 – Larry W SmithThis is a common formatting issue in the back office systems in my niche.
Thanks again,
PaulMay 1st, 2009 at 10:35 am -
john said:
Hi Paul…
If the names are consistantly in one of those three formats, you could easily probably write different formulas for the different types of name formats to extra the names correctly.
I have not posted an example like this yet, but if you want to shoot me an email with an example of what you’re looking for I can take a look.
May 1st, 2009 at 10:43 am -
Ryan said:
I am not having luck with these formulas in 2003, is this a new to 2007 feature?
July 14th, 2009 at 5:20 pm -
Ryan said:
Nevermind on the above comment I found a slight variation that seems to work for me. Thanks for the tip ive been trying to find something similar all day.
To get last name
=MID(A1,FIND(” “,A1)+1,LEN(A1))To get first name
=LEFT(A1,FIND(” “,A1))July 14th, 2009 at 5:26 pm -
Jazz said:
Great formula, brought back many memories of when I used to work for a mailing house and I used this code almost everyday as the data most clients sent was awful.
It’s simple and very very effective, even when dealing with 1,000’s of records.
September 8th, 2009 at 6:50 am -
Greg said:
Hi,
Here is another way to get the Last name (or word):
=TRIM(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))))))
October 7th, 2009 at 4:50 pm -
john said:
Good one Greg!
October 7th, 2009 at 9:28 pm -
Chantal said:
Thanks, this help was wonderful!
October 22nd, 2009 at 2:43 pm -
Rick said:
Genius Greg, thanks!
November 2nd, 2009 at 12:49 pm -
tom said:
good tip. thanks for sharing.
January 26th, 2010 at 5:11 pm
Loading ...
[...] post can be seen as a follow up to an earlier post, Separate Full Name into First & Last Name. Excel provides a very easy to use tool to separate delimited data into multiple columns. All [...]