Separate Full Name into First & Last Name
- 34 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
34 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 -
Rich said:
Hi there,
Firstly, great suggestions, very helpful. However, I’m still slightly stuck..
I have one column with names in this format
Jones, Tom
Blogs, Joe
etcI need to create a new column in the following format
JonesT
BlogsJCan anybody help ??
Many thanks
R
March 17th, 2010 at 11:00 am -
john said:
Hi Rich, try this formula. There are probably several ways to do that, but here’s one:
=LEFT(A1,FIND(“, “,A1)-1)&MID(A1,FIND(“, “,A1)+2,1)
March 17th, 2010 at 12:46 pm -
Rich said:
John, that is tremendous! Does exactly what i need it to! Not an excel person, that would have taken me weeks to figure out
Many thanks
Rich
March 17th, 2010 at 1:00 pm -
Louisa said:
Thanks, that’s very helpful. However, I have a report that have all different kinda names, including personal names and company names. i.e:
1. ABC, LLC
2. ABC Corporation
3. Doe David
4. Doe Mary C.
5. ABC TrustFor those company and Trust names, I want to leave it as it is, no need to split last name and first name, but for those personal names,I do want to split last and first names. Is there any function that I could fix the whole column?
April 16th, 2010 at 4:17 am -
tanya said:
How about changing Smith, John to John Smith?
Any help would be appreciated!!
thanks, tanya
April 22nd, 2010 at 3:54 pm -
sarina said:
Hi.
I would so appreciate the formula to turn:John Smith
into
Smith, John
Thanks!
April 29th, 2010 at 2:49 pm -
john said:
Try this:
=RIGHT(A1,LEN(A1)-FIND(” “,A1)) &”, “&LEFT(A1,FIND(” “,A1)-1)
April 29th, 2010 at 2:53 pm -
sarina said:
Hi.
Thank you for the help already.
Just thought I’d try again, if anyone knows how to take something like this:John Smith
3456 Doe Street
Kalamazoo, ZK 35605and turn it into 5 columns (one just for the name, one just for the street address, one for city, one for state, one for zip
I get tons of info that looks like the above, but need to enter it in 5 columns, and I fear some carpal tunnel.
My best bet so far has been to cut & paste it individually, but I wondered if I enter it all at once (as is, in one clump) if there was a way to get Excel to break it up for me.
Any advice/info would be greatly appreciated.
Thanks!
SarinaMay 10th, 2010 at 10:39 am -
john said:
Hi Sarina,
Are you placing all of that into 1 cell currently?
May 10th, 2010 at 11:22 am -
sarina said:
I cut and paste it into 5 cells, but would love to drop it in one cell and then have it broken up into the 5 cells for me or whatever else you can recommend. It comes in that arrangement as written – 3 lines of info.
May 10th, 2010 at 12:06 pm -
tanya said:
thank you for the
=RIGHT(A1,LEN(A1)-FIND(” “,A1)) &”, “&LEFT(A1,FIND(” “,A1)-1)formula!
I’m able to “Smith, John” into “John Smith,”. Can you tell me how to remove the comma at the end?
Please and thank you!!
tanyaMay 20th, 2010 at 2:08 pm -
john said:
Hi Tanya,
There are many ways to do this, try this:
=RIGHT(A1,LEN(A1)-FIND(“,”,A1)-1) &” “&LEFT(A1,FIND(“,”,A1)-1)
You might also want to look into the Text to Columns feature of Excel.
May 20th, 2010 at 2:17 pm -
tanya said:
JOHN YOU ARE AWESOME!!!!
Thank you so much for the quick response and saving my hours of copy&pasting!!!
May 20th, 2010 at 2:27 pm -
Ajay said:
if i have name like Ajay Kumar Yadav and i need to separate every name like
Ajay
Kumar
YadavPlease advise
June 24th, 2010 at 12:34 am -
Deb said:
I have one, my info is John A. Smith, MD and I need it to be Smith, John A DO or even into indivicual columns, whichever is easier. Anyone able to help me with this? Thanks much for looking!
June 25th, 2010 at 9:37 am -
Bis said:
To seperate the last name, I use:
=RIGHT(A1,LEN(A1)-FIND(” “,A1))Sometime the names can appear as FirstName:LastName in stead of a space in-between, so the seperator can be updated in the formula
=RIGHT(A1,LEN(A1)-FIND(“:”,A1))June 28th, 2010 at 8:34 am -
BrianK said:
I use this for splitting things such as location/person where I have a timesheet of information and I need to just know the locations/names serperately.
if you have Plant32/Johnson in cell a1, we’ll call a1 the source
….johnson is working in plant32…for the location:
=left(source,find(“/”,source)-1)
where source is the cell which contains the information you want to splitfor the names:
=right(source,(len(source)-find(“/”,source)))
where source is the cell which contains the information you want to splitperhaps there is a different way. i use this to track 15 or so substitute custodians in a school system for time sheet, etc. anyone interested I can e-mail the sheets.
July 13th, 2010 at 7:47 pm -
BrianK said:
Deb,
for John A. Smith, MD
in ecel 2007 you could use Convert text to table
select space as the delimeter
then you would have them in seperate columns, however it would look as you see it… John A. Smith, MD where each space = new column. Moving the Smith column could be done easily enough or did you need a formula to simply do the whole thing?July 13th, 2010 at 7:57 pm -
BrianK said:
Ajay,
Here is what I tried, to seperate the name, assuming that is a middle name or whatever, use these formulas. We’ll call the location of Ajay Kumar Yadav the “source”
To seperate the parts I’ll call them LeftPart, CenterPart, RightPartfor the LeftPart use:
=LEFT(source,FIND(” “,source)-1)for the CenterPart use:
=IF(ISERR(FIND(” “,source,1+FIND(” “,source))),”",MID(source,FIND(” “,source)+1,FIND(” “,source,1+FIND(” “,source))-FIND(” “,source)-1))for the RightPart use:
=IF(ISERR(FIND(” “,source,1+FIND(” “,source))),MID(source,FIND(” “,source)+1,LEN(source)),MID(source,1+FIND(” “,source,1+FIND(” “,source)),LEN(source)))phew… hope that helps.
Put the formulas in the column where you want to split the name upJuly 13th, 2010 at 8:20 pm -
Chris said:
I am using this formula =LEFT(A6,FIND(” “,A6)-1) on this text located in A6: 1001 Bermuda Bag Alligator. I would like to separate the 1001 from the rest of the text. I receive this error message #NAME.
I am not sure what I am doing wrong? I am using excel 2007.
August 17th, 2010 at 9:22 am
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 [...]