Jan 24 2009

Separate Full Name into First & Last Name

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

Did you find this post helpful? Try signing up for our free daily tips.

TAGS:

1. Using Text to Columns to Separate Data | Excel Hints wrote:

[...] 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 [...]

January 29th, 2010 at 1:07 am
2. Nicole Wilker » Excel – separating first and last name from a single column wrote:

[...] found it Excel Hints what a concept eh?  Better suscribe to that blog right [...]

January 11th, 2011 at 7:04 pm
3. excel formula to separate first and last name? | Askjis wrote:

[...] Excel Tips and Tricks for All Levels … 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. – read more [...]

July 25th, 2013 at 8:05 pm
1. 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
2. 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
3. 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
4. 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 Smith

This is a common formatting issue in the back office systems in my niche.

Thanks again,
Paul

May 1st, 2009 at 10:35 am
5. 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
6. 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
7. 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
8. 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
9. 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
10. john said:

Good one Greg!

October 7th, 2009 at 9:28 pm
11. Chantal said:

Thanks, this help was wonderful!

October 22nd, 2009 at 2:43 pm
12. Rick said:

Genius Greg, thanks!

November 2nd, 2009 at 12:49 pm
13. tom said:

good tip. thanks for sharing.

January 26th, 2010 at 5:11 pm
14. 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
etc

I need to create a new column in the following format
JonesT
BlogsJ

Can anybody help ??

Many thanks

R

March 17th, 2010 at 11:00 am
15. 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
16. 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
17. 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 Trust

For 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
18. tanya said:

How about changing Smith, John to John Smith?

Any help would be appreciated!!

thanks, tanya

April 22nd, 2010 at 3:54 pm
19. sarina said:

Hi.
I would so appreciate the formula to turn:

John Smith

into

Smith, John

Thanks!

April 29th, 2010 at 2:49 pm
20. john said:

Try this:

=RIGHT(A1,LEN(A1)-FIND(” “,A1)) &”, “&LEFT(A1,FIND(” “,A1)-1)

April 29th, 2010 at 2:53 pm
21. 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 35605

and 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!
Sarina

May 10th, 2010 at 10:39 am
22. john said:

Hi Sarina,

Are you placing all of that into 1 cell currently?

May 10th, 2010 at 11:22 am
23. 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
24. 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?

tanya

May 20th, 2010 at 2:08 pm
25. 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
26. 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
27. Ajay said:

if i have name like Ajay Kumar Yadav and i need to separate every name like
Ajay
Kumar

June 24th, 2010 at 12:34 am
28. 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
29. 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
30. 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 split

for the names:
=right(source,(len(source)-find(“/”,source)))
where source is the cell which contains the information you want to split

perhaps 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
31. 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
32. 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, RightPart

for 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 up

July 13th, 2010 at 8:20 pm
33. 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
34. Shebra said:

Can anyone provide a formula for “Smith,John”? There are no spaces. I would like the last name in a seperate cell minus the coma.

September 23rd, 2010 at 12:20 pm
35. Faizal said:

I have a 14 character no eg: 20001016862200, from this I have to extract 101686 the formula will be =MID(A5,4,6). But I need the result in 19861610 instead of 101686.That last two characters 86 prefixed by value “19″, then mid value 16 and the last value 10.Can anybody help me on this. Thanks in advance

November 15th, 2010 at 8:01 am
36. john said:

Try this…

=”19″&MID(A5,9,2)&MID(A5,7,2)&MID(A5,5,2)

November 15th, 2010 at 5:12 pm
37. andrea said:

Everytime I try to use the formulas above I keep getting #NAME?

Can someone help tell me what I’m doing wrong?

November 19th, 2010 at 11:59 am
38. javie said:

to get the firstname try this =LEFT(A1,LEN(A1)-FIND(” “,A1,1))

January 6th, 2011 at 9:36 am
39. andrea said:

andrea,

retype the ” ” in each instance. they are intalicized for some reason when you cut/paste.

January 8th, 2011 at 3:41 pm
40. Linda said:

How can I change “Smith, Linda A” into “Linda Smith”? Thanks!

January 11th, 2011 at 8:22 pm
41. tom said:

i already split the first name and last name, what if there is in the list that include only the last name(without last name)
example:

1 john smith
2 mark twain
3 arnold

i used LEFT(A1,FIND(” “,A1)-1) for 1 and 2,is there a formula for me to get the first name onlly for no.3 using the same formula for 1 and 2?? because when i used the formula for no3 i get an error.

thanks

January 14th, 2011 at 7:55 am
42. John From Raleigh said:

I ended up using the text to columns feature and it worked great. I have Excel 2003.

Thanks for the tip….

January 18th, 2011 at 1:04 pm
43. Rachael said:

I have cells where the Name field was pulled from an external database. The names show up like this: JohnSmith. No space between first and last name, however the start of the last name is always in caps. How can I separate these into separate cells that read John Smith? I need a column heading of First Name and a column heading of Last Name, but there are over 5,000 names and I cannot go through each individual cell to separate them! Also, there are blank rows between each record, is there a way I can delete every other row without having to highlight each individual one? This is a huge spreadsheet and I am trying to complete it quickly and with accuracy. Thanks!

January 19th, 2011 at 12:16 pm
44. Daniel said:

Hi, this is great, but your website seems to convert straight quotes into smart quotes (curly quotes). The quotation marks must be straight quotes or else Excel gives a #NAME? error. It’s the content management system or rich text editor on the site that usually does that. Probably worth fixing since it seems like this problem would happen a lot on this kind of site.

February 2nd, 2011 at 9:21 pm
45. Jamie said:

Not working for me. I keep getting the ?Name error in my cell.

Using Excel 2007.

February 8th, 2011 at 8:00 pm
46. Meena said:

I spent quite a bit of time experimenting the above samples. It didn’t work for me. All I wish to do is separate/reverse this in column A2 of my data sheet: Loretta Smith
I tried this string =MID(A2& “, ” &A2, FIND(” “,A2) +1, LEN(A2)-1), but it keeps returning a partial result of her First Name such as
(Smith, Loret). What am I missing from the formula to have it include the full spelling of this name?

Any help is greatly appreciated. Thanks!

March 2nd, 2011 at 7:53 pm
47. Ellen said:

I have tried all of the above option, I have changed manually the smart quotes into straight quotes and I still get #name? Anybody got a solution as I dont want to split 350 names in first names and surnames manually!

June 8th, 2011 at 10:21 am
48. john said:

Shoot me an email from the contact form with what you tried and let me take a look… I know there’s been a problem with the quotes on the site, I’m trying to fix soon hopefully.

June 8th, 2011 at 11:09 am
49. Ellen said:

June 9th, 2011 at 3:14 am
50. vinay said:

I have a name called “Sanjay Kumar Gupta”…i want to get firstname and middlename in one column and lastname in second column. can anybody help in getting it

July 8th, 2011 at 2:24 am
51. john said:

Look into “Text to Columns” feature of Excel… with space as the delimiter.

July 8th, 2011 at 9:34 pm
52. Vinicius Camara said:

First of all, congratulations. This post is awesome!
In my excel works but I needed change function separators:
=LEFT(A2;FIND(” “; A2)-1)
=RIGHT(A2;LEN(A2)-FIND(” “;A2))

Thank you.

August 3rd, 2011 at 11:24 am
53. Varun said:

If we use =LEFT(A2;LEN(A2)-FIND(” “;A2)) to get the first name then why dont get the entire first name.
i am not an excel person so would be great if the similar formula is used for both left and right words.

August 4th, 2011 at 8:46 am
54. Lily said:

Hi John,

I’m working on a project with a tight deadline.

Can you kindly let me know what the formula would be for converting
KENDALL J. Smith

to

Smith, KENDALL J.

August 15th, 2011 at 3:16 pm
55. john said:

Try something like this to find the last space, then to the left is the first/middle and to the right is the last name…

=LEN(A1)-LEN(RIGHT(A1,LEN(A1)-SEARCH(“@”,SUBSTITUTE(A1,”
“,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))))

August 16th, 2011 at 10:27 am
56. Jessica said:

Is it possible to get First Name Last Name format from multi-entry data, such as in example below:

Lepter David|Nilesh Raj|Swen Michael|Kamil Raj|Ouder Gerald|Mick Bryan|Tout Frank|Egrin Joseph|Hartman Phil

Format needed would be:

David Lepter|Raj Nilesh|Michael Swen|Raj Kamil|Ouder Gerald|Bryan Mick|Frank Tout|Joseph Egrin|Phil Hartman

Thanks very much for any input you may have on this.

August 21st, 2011 at 9:56 am

I have a name called “ram bhakta shrestha subedi”.
I want to extract only the last word subedi.
how can i do this?

September 7th, 2011 at 4:30 am
58. Tim said:

I have two challenges in some data cleansing of a list which includes 15,000 entries. Therefore, any help would be greatly appreciated.

1. I need to separate Last from First. No space but the first name is Capitalized. Is there anyway to identify a capital letter then move the rest of the name to another column. Here’s a sample:

MerrittBecky
LamoreauxRebecca
KageBrian
BelcherGordon
EliasonWilliam
SkillenBelinda
ElliottBlake

2. I have emails that have double entries. Therefore, is there a way to cut off all existing letters after ‘.com”

Many thanks for help.

Tim

September 12th, 2011 at 2:59 pm
59. Dayana said:

HI

James p kamath

Antony pinto Alex

D Anjelena Fetura margret

thanks
dayana meann

September 22nd, 2011 at 9:10 am
60. asas said:

Hi. Could somebody help me with this? I have name John Smith and I need put in another cell but like this “[John][Smith]“

October 24th, 2011 at 11:47 am
61. Rose said:

For some reason, I cannot get the formulas to work. The answer I get is: #NAME?

Can you help? Thanks.

October 26th, 2011 at 3:57 pm
62. Julie said:

I am working on a complex list of names such as

Mr & Mrs Douglas Winston
Mr. John F. Smith
Dr. & Mrs. Jack F. Doe

I just want to separate the last names so I can sort by last name. All the formulas I’ve tried give me more than just the last name.

How can I do this? Using Excel 2007.
Thanks

October 31st, 2011 at 1:59 pm
63. Christina Haller said:

I need to separate John & Amy Smith from one cell to John & Amy in one cell and Smith in the second. Any tips?

November 4th, 2011 at 3:05 pm
64. Karen Schrimpf said:

I too need to find out how to separate a couples 1st names out of a mailing list to be used in a mail merge for a salutation. for example
Ken & Barbie Doll – I just need Ken & Barbie.

November 7th, 2011 at 1:14 pm
65. john said:

To get the last word in a cell try using a formula such as this: =RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))) . That is assuming the last name is only a single word and is at the end (suffixes can mess that idea up)

November 8th, 2011 at 7:41 am
66. john said:

I would use the Text To Columns feature of excel and use space as the delimiter. Then you can work on recombining what you want. Or possibly using the formula: =RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))) to find the last name in combination with the find & left formulas to only show the first names.

November 8th, 2011 at 7:43 am
67. My Hoa said:

Thank you very much john. You’re so genius^^

November 8th, 2011 at 11:14 pm
68. Sanjeev said:

Hi,
I Just want know how to change Last Name as first in single cell.

Example.

Sanjeev Shrikant Jahagirdar

now i want to change some thing like below

Jahagirdar Sanjeev Shrikant

Regards
S.S.Jahagirdar

November 12th, 2011 at 2:25 am
69. ABDULSALAM said:

I need command for middle name (example: firstname + middle name + last name.

pls show me how to split it

December 15th, 2011 at 2:00 am
70. ABDULSALAM said:

Please show me how to split this full name to first name, middle name & last name

DULAL SHIKDER MD AYNAL DULAL SHIKDER MD AYNAL

December 15th, 2011 at 2:02 am
71. SACHIN said:

What is the last cell name in M.S. Excel Worksheet?

December 27th, 2011 at 10:39 am
72. MAV said:

Hi – I have a formula that references a cell in a name column for selecting a same-named worksheet tab as part of a cell-reference in the same-named worksheet. The formula is as shown below and works great – except when the name is one that includes a quote mark, e.g. Frank O’Hara. How to adjust formula to fix this problem?

=SUM(INDIRECT(“‘”&A2&”‘!\$V\$57″))

January 25th, 2012 at 11:52 am
73. kaia said:

how can i convert Smith, John Joe X into X., John Joe, Smith?

February 19th, 2012 at 9:23 am
74. kaia said:

how can i convert Smith, John Joe X into X., John Joe, Smith?

February 19th, 2012 at 9:25 am
75. Omar said:

@MAV

In the worksheet in which you have the formula <<> set up — make the following minor change.

In the name column, search for all single quotes (‘) and replace them with 2 single quotes (”).
Note: It is not a double quote. They are 2 single quotes put beside each other without space between them.

This will totally resolve the issue that you face. Please let me know if this does not.

>> Omar

February 22nd, 2012 at 1:00 pm
76. Omar said:

@Julie, this can be done only through VBA code and it is mentioned below.

Important notes:
1. Code assumes that every last name has a ” ” (empty space) before it
eg: Mr & Mrs Douglas Winston >> code will return >> Winston

2. If the value in a cell does not have any spaces, code assumes that it is the last name and returns the entire content
eg: Mr.NoName >> code will return >> Mr.NoName (i.e. original value)

3. Code assumes that last name of a person is at the end of the string
eg: Mr. and Mrs. John Doe >> code assumes >> Doe is the last name >> and returns >> Doe

Steps to make this work in your Excel sheet:
————————————————-
1. Copy the below code and paste it in a module within “Visual Basic” editor of your Excel workbook
2. Insert a blank column after the column which has the names listed
3. In the first cell, copy-paste “=Find_Last_Occur(” “,A1)”
Note1: without the quotes
Note2: A1 is with the assumption that your names are listed in column A. Feel free to change it to reflect your own column and code will work fine
Note3: Find_Last_Occur is a user defined function i.e. the code written for resolving this
4. Drag the formula across the length of the names list so that, it can work for all the names
5. If you are using Excel 2007 / 2010 >> and want to save the VBA code, you need to save the workbook with .xlsm extension. Else, you need to repeat steps 1 to 3 each time

Code to be saved in the VB Editor is mentioned below:
———————————————————–
Function Find_Last_Occur(sFind As String, _
sInput As String) As String

Dim Jr As Integer, Mid_Val As Integer, Find_Last As Integer
Dim len100 As Long

Application.Volatile
Find_Last = 0
Mid_Val = 0

For Jr = 1 To 1000
Mid_Val = InStr(Mid_Val + 1, sInput, sFind)
If Mid_Val = 0 Then Exit For
len100 = Len(sInput)
sInput = Right(sInput, len100 – Mid_Val)
If Mid_Val 0 Then Find_Last_Occur = Find_Last + 1
Mid_Val = 0
Next Jr
Find_Last_Occur = sInput
If Find_Last = 0 Then Exit Function

End Function

February 22nd, 2012 at 4:37 pm
77. Omar said:

@Julie, this can be done only through VBA code and it is mentioned below. Came to this site only today. Hope you still find this solution useful

Important notes:
1. Code assumes that every last name has a ” ” (empty space) before it
eg: Mr & Mrs Douglas Winston >> code will return >> Winston

2. If the value in a cell does not have any spaces, code assumes that it is the last name and returns the entire content
eg: Mr.NoName >> code will return >> Mr.NoName (i.e. original value)

3. Code assumes that last name of a person is at the end of the string
eg: Mr. and Mrs. John Doe >> code assumes >> Doe is the last name >> and returns >> Doe

Steps to make this work in your Excel sheet:
————————————————-
1. Copy the below code and paste it in a module within “Visual Basic” editor of your Excel workbook
2. Insert a blank column after the column which has the names listed
3. In the first cell, copy-paste “=Find_Last_Occur(” “,A1)”
Note1: without the quotes
Note2: A1 is with the assumption that your names are listed in column A. Feel free to change it to reflect your own column and code will work fine
Note3: Find_Last_Occur is a user defined function i.e. the code written for resolving this
4. Drag the formula across the length of the names list so that, it can work for all the names
5. If you are using Excel 2007 / 2010 >> and want to save the VBA code, you need to save the workbook with .xlsm extension. Else, you need to repeat steps 1 to 3 each time

Code to be saved in the VB Editor is mentioned below:
———————————————————–
Function Find_Last_Occur(sFind As String, _
sInput As String) As String

Dim Jr As Integer, Mid_Val As Integer, Find_Last As Integer
Dim len100 As Long

Application.Volatile
Find_Last = 0
Mid_Val = 0

For Jr = 1 To 1000
Mid_Val = InStr(Mid_Val + 1, sInput, sFind)
If Mid_Val = 0 Then Exit For
len100 = Len(sInput)
sInput = Right(sInput, len100 – Mid_Val)
If Mid_Val 0 Then Find_Last_Occur = Find_Last + 1
Mid_Val = 0
Next Jr
Find_Last_Occur = sInput
If Find_Last = 0 Then Exit Function

End Function

February 22nd, 2012 at 4:37 pm
78. Omar said:

@Kaia: My solution will work only if the format is similar to what you mentioned in this website.

Steps:
1. Copy the below mentioned formula and paste it in the worksheet cell of your choice
2. Make the necessary changes in the formula to ensure that you are referring to the appropriate cell
3. Here, I am assuming that the name is listed in A1
4. After copy-paste and necessary changes, drag the formula across the worksheet as per your choice

Formula:
=RIGHT(A1,LEN(A1)-FIND(” “,A1,1))&”, “&MID(A1,1,FIND(” “,A1,1)-2)

Note:
1. If there are any leading / succeeding spaces, suggest you to use wrap my formula in TRIM function of Excel to get appropriate results
2. Sorry, if the list of steps sound rudimentary — wanted to be as clear as possible

Let me know how it goes…

February 22nd, 2012 at 4:56 pm
79. Omar said:

@Man Prasad Chowai: If you are still interested, solution that I provided to Julie today should work for you as well.

Please check and let me know.

February 22nd, 2012 at 4:59 pm
80. arun said:

Hi ,,
I have a data like this
Web TimeSheet SaaS Time and Attendance (12 Months) 1/1/2012 to 12/31/2012
Web TimeSheet SaaS Project & Billing (wrong date range) (1 Month) 12/02/2012 to 12/03/2012

I wanted to segregated only ( 12/02/2012 to 12/03/2012) date please help me any one for this through Excel formula.

Thanks
Arun

March 8th, 2012 at 7:24 pm
81. cap said:

thanks heaps found this to be very helpful

April 19th, 2012 at 7:23 pm
82. Marie said:

I have a Excel spreadsheet that has a single column with a house number and street name. I need to separate each into a separate column. I have only 17000 addresses to separate. Any suggestions? I have tried to the Text to Column function, but keep getting the error message of “#NAME?”.

May 1st, 2012 at 4:37 pm
83. Raju said:

Hi All,

how can i find city name from this data

SWAPNANURSINGHOMENISHATHBAGHBEUMPET HYD
ATOLICHOWKIFATIMACOLY  HAKEEMPETHYD
HNOGOKULNAGARTARNAKA  SD
RES:8-3-835/836FLAT NO502 YELLAREDDYGUDAOPP TO    COMMUNITY HALL, HYDE
FLAT NO-405 3-6-460 GOKULKUNJ STREET 5 HARDIKARBAGH HIMAYATHNAGAR    HYDERABAD-500029

May 8th, 2012 at 8:10 am
84. Jason said:

I had a problem with surnames containing spaces (e.g. Pedro de la Rosa, or Paul di Resta). But I’ve come up with a really simple solution.

I took the LENgth of the first name (+ the space) away from the LENgth of the entire string, and use the remainder in the RIGHT command:

=RIGHT(A1,LEN(A1)-FIND(” “,A1))

So “Pedro de la Rosa” becomes “de la Rosa”.

Hope this helps you guys, it worked for me.

May 27th, 2012 at 7:40 am
85. Jason said:

Also, if you wanted to add the first initial, you could modify it to this:

=LEFT(A1,1)&”. “&RIGHT(A1,LEN(A1)-FIND(” “,A1))

This gets the first character of the name [ LEFT(A1,1) ] adds a full stop and space [ &". " ] and then adds the original formula for multiple surnames [ &RIGHT(A1,LEN(A1)-FIND(" ",A1)) ].

Peace x

May 27th, 2012 at 7:55 am
86. ben peck said:

For conversion of Last, First –> First Last use

=CONCATENATE(RIGHT(A5,(LEN(A5)-(FIND(“,”,A5)+1))),” “,LEFT(A5,(FIND(“,”,A5))-1))

were a5 is the cell where last, first is.

July 19th, 2012 at 11:19 am
87. Ashwin Koshti said:

Another option for seperate first name and last name is Text to Column. By using this function, you can seperate the first name and last name very quickly and easily.

August 28th, 2012 at 1:43 am
88. Heather C said:

I am working on cleaning up a spreadsheet where the first and last is entered with no space. ex. JohnSmith. How do i seperate them out so that John is one column and Smith is another??

August 30th, 2012 at 3:26 pm
89. Rocky said:

Hi Heather if you’re still interested & for anyone else, the following beastly formulas should work to separate first & last names when the data contains only first & last names in one cell where the First & Last names start with capital letters & there are no spaces between the names:

First Name – =IFERROR(LEFT(A1,FIND((IFERROR(IF(FIND(“A”,A1,2),”A”),IFERROR(IF(FIND(“B”,A1,2),”B”),IFERROR(IF(FIND(“C”,A1,2),”C”),IFERROR(IF(FIND(“D”,A1,2),”D”),IFERROR(IF(FIND(“E”,A1,2),”E”),IFERROR(IF(FIND(“F”,A1,2),”F”),IFERROR(IF(FIND(“G”,A1,2),”G”),IFERROR(IF(FIND(“H”,A1,2),”H”),IFERROR(IF(FIND(“I”,A1,2),”I”),IFERROR(IF(FIND(“J”,A1,2),”J”),IFERROR(IF(FIND(“K”,A1,2),”K”),IFERROR(IF(FIND(“L”,A1,2),”L”),IFERROR(IF(FIND(“M”,A1,2),”M”),IFERROR(IF(FIND(“N”,A1,2),”N”),IFERROR(IF(FIND(“O”,A1,2),”O”),IFERROR(IF(FIND(“P”,A1,2),”P”),IFERROR(IF(FIND(“Q”,A1,2),”Q”),IFERROR(IF(FIND(“R”,A1,2),”R”),IFERROR(IF(FIND(“S”,A1,2),”S”),IFERROR(IF(FIND(“T”,A1,2),”T”),IFERROR(IF(FIND(“U”,A1,2),”U”),IFERROR(IF(FIND(“V”,A1,2),”V”),IFERROR(IF(FIND(“W”,A1,2),”W”),IFERROR(IF(FIND(“X”,A1,2),”X”),IFERROR(IF(FIND(“Y”,A1,2),”Y”),IFERROR(IF(FIND(“Z”,A1,2),”Z”),”-”))))))))))))))))))))))))))),A1,1)-1),”-”)

Last Name – =IFERROR(MID(A1,FIND((IFERROR(IF(FIND(“A”,A1,2),”A”),IFERROR(IF(FIND(“B”,A1,2),”B”),IFERROR(IF(FIND(“C”,A1,2),”C”),IFERROR(IF(FIND(“D”,A1,2),”D”),IFERROR(IF(FIND(“E”,A1,2),”E”),IFERROR(IF(FIND(“F”,A1,2),”F”),IFERROR(IF(FIND(“G”,A1,2),”G”),IFERROR(IF(FIND(“H”,A1,2),”H”),IFERROR(IF(FIND(“I”,A1,2),”I”),IFERROR(IF(FIND(“J”,A1,2),”J”),IFERROR(IF(FIND(“K”,A1,2),”K”),IFERROR(IF(FIND(“L”,A1,2),”L”),IFERROR(IF(FIND(“M”,A1,2),”M”),IFERROR(IF(FIND(“N”,A1,2),”N”),IFERROR(IF(FIND(“O”,A1,2),”O”),IFERROR(IF(FIND(“P”,A1,2),”P”),IFERROR(IF(FIND(“Q”,A1,2),”Q”),IFERROR(IF(FIND(“R”,A1,2),”R”),IFERROR(IF(FIND(“S”,A1,2),”S”),IFERROR(IF(FIND(“T”,A1,2),”T”),IFERROR(IF(FIND(“U”,A1,2),”U”),IFERROR(IF(FIND(“V”,A1,2),”V”),IFERROR(IF(FIND(“W”,A1,2),”W”),IFERROR(IF(FIND(“X”,A1,2),”X”),IFERROR(IF(FIND(“Y”,A1,2),”Y”),IFERROR(IF(FIND(“Z”,A1,2),”Z”),”-”))))))))))))))))))))))))))),A1,2),20),”-”)

September 15th, 2012 at 3:19 pm
90. Ashwini said:

=MID(A1,FIND(” “,A1,1)+1,LEN(A1))
can you plz explain this formula
and i want a formula for first name intial and last name.

October 18th, 2012 at 12:09 am
91. George said:

Both formulas from the original article return #NAME errors in Excel 2010. Any help would be appreciated.

=LEFT(A1,FIND(” “,A1)-1)
=RIGHT(A1,LEN(A1)-FIND(” “,A1))

March 3rd, 2013 at 5:38 pm
92. Shanice said:

I have a row of names that doesn’t have pattern on left column is the Surname. eg.
1. Vincent Lee Pak Kuan (Lee is the Surname, Vincent, Pak Kuan is First Name)
2. Cheong Lin Yee (Cheong is the Surname, Lin Yee is First Name)

How do I work from this

May 14th, 2013 at 11:53 pm
93. visit this web page link said:

Wow, marvelous blog layout! How long have you been blogging
for? you made blogging look easy. The overall look of your web site is wonderful, as well as
the content!

June 5th, 2013 at 7:39 am

My spouse and I absolutely love your blog and find a lot of your post’s
to be exactly I’m looking for. Does one offer guest
writers to write content available for you? I wouldn’t mind
publishing a post or elaborating on most of the subjects you write with regards to here.
Again, awesome web log!

December 18th, 2013 at 5:49 pm
95. Theo Witteveen said:

FN
MBB_U
D___U
These symbols will have to be FN.TO, MBB-UN.TO AND
D-UN.TO I don’t have a problem with .TO or -UN.TO but the different number of _ (under strikes) used. They vary between 0 and 3.
What to do when the number is zero and how to count the number of ___? Thanks a lot. Your site is great!

Theo Witteveen

July 8th, 2014 at 11:47 am
96. Avnesh Chaudhary said:

plz tell me how to split first and last name if they are combined with any character other than letters. E.G.Avnesh.chaudhary
vishal1mittal
akash#sharma
Prateek_bansal
plz help me..plz

August 20th, 2014 at 12:25 pm
97. ExcelHints said:

http://www.vbaexpress.com/forum/forum.php

August 20th, 2014 at 2:01 pm
98. RAMESH R said:

HI,

I HAVE A NAME IN CELL A1 (ARUN KUMAR GUPTA). I NEED OUT PUT IN CELL B1 AS (ARUN K GUPTA,ARUN GUPTA,GUPTA ARUN,ARUN KUMAR,ARUN KUMAR G,G ARUN KUMAR,A K GUPTA).

KINDLY SHOW ME WITH FORMULA HOW TO SPLIT “ARUN KUMAR GUPTA” WITH VARIOUS NAME COMBINATIONS AS I MENTIONED ABOVE INTO SINGLE CELL WITH COMMA(,).

CAN ANYONE HELP ME TO SPILT AS I MENTIONED ABOVE.

August 6th, 2015 at 10:44 am
99. shelly said:

HOW we can split name into different2 column?

example

MrSohanSharma

August 23rd, 2015 at 12:32 am
Subscribe Form

Subscribe to Blog

Free updates with Excel Hints, Tips & Tricks