If you have a list of names in Excel, with first and last names separated by a comma, you can use an Excel feature to split first and last names into separate columns.
See more ways to split names, and get an Excel sample workbook, on the Names, Split/Reverse First and Last page on my Contextures site.
Select the List
To start, select the cells that contain the names.
Text to Columns
On the Ribbon in Excel 2007, click the Data tab, then click Text to Columns.
If you’re using an earlier version of Excel, click the Data menu, then click Text to Columns.
The Convert Text To Columns Wizard
The Convert Text to Columns Wizard opens, and you can select the type of data that you want to convert.
Note: The wizard layout varies slightly in different versions of Excel, but the steps are the same.
- The list in this example uses a comma to separate the names, so select Delimited, then click Next.
- Next, add a check mark to the delimiter that’s used in your text. You can select more than one, but in this example only Comma is required. We only want to split first and last names were the comma is inserted.
- Click Next, to go to Step 3.
- Select a destination cell, where you want the split text to start. The default setting is to overwrite the existing data, so if you want to keep it, select a different cell as the destination.
- Click Finish, and the data is split into separate columns.
Other Methods For Splitting Data
This method is quick and easy, and perfect for a one time event, such as importing a list of names, and splitting it.
However, if the names will change frequently, you could use a formula to split the names, instead of the Text to Columns feature. Then the first and last name columns will automatically update, if the full name changes.
More Ways to Split Names
See more ways to split names, and get an Excel sample workbook, on the Names, Split/Reverse First and Last page on my Contextures site.
________________________
I cannot tell you how often I have to deal with this. We have an application at work which dumps the names out like this, and when we try and turn them around for a mailing list, it has to be cleaned up.
Just two notes for people that do this, however. One, consider carefully your source: watch out for multiple commas or “Company, Inc.” Two, the space after the comma in the original text will be at the beginning of the cell after the text is separated. Separating the fields is important, but it is not the last step in cleaning up the data for use elsewhere.
Thanks David, that’s good advice. Data cleanup can be a real challenge.
A great service you’re providing here, Debra. Thank you.
I have an EXCEL problem for you. I want to enter one of three channels (A,P,D) in A1 and a ZIP code in B1 and render the appropriate sales rep in C1.
I won’t have 99,999 ZIP rows. Instead, I’ll know who covers ZIP 17100 to 17200 and so on.
Some kind of Pivot table, I imagine. It’s way over my level of expertise … but probably just right for you.
http://twitter.com/joehageonline
Thanks Joe, glad you like the blog.
For your sales reps you could use a VLOOKUP formula, similar to the one shown here:
http://www.contextures.com/xlFunctions02.html#Range
Put the starting zip codes for each range in the first column and rep names in the second column. Then refer to that table in the VLOOKUP formula.
Since we’re talking about splitting, here is a quick little VBA routine I wrote up to split names. It will split on any character, and can return the n’th result from the split.
‘———————————–
Function Split2(SourceString As String, SplitChar As String, Index As Long) As String
Dim x() As String
x = Split(SourceString, SplitChar)
If UBound(x) < Index – 1 Then
Split2 = “”
Else
Split2 = x(Index – 1)
End If
End Function
‘———————————–
Hope it’s helpful.
-John
[…] 2009. Email This article to a Friend Debra Dalgleish at Contextures blog wrote an article Split First and Last Names in Excel. It gave me an idea about creating a formula that split the names and then also sort the names […]
Hi Debra,
Just wanted to thank you for the help you provided me today. I did a quick search to find how to switch the names and found your blog right away. The job was completed within minutes. You were a life saver!
Zoey, that’s great! Thanks for letting me know that the instructions helped.
hi there to splitDiff, i would like to ask how to use your codes? i cannot call it in excel.
Hi there,
I need to extract the last word in a cell regardless the length of and position of it in a cell.
It is like:
Cell A1: Test Excel Office 2007
Cell A2: Test Excel Office Functions 2007
I want a formula to extract 2007 from both cells.
Can anyone help. Would be grateful if you forward comments to [email protected]
Thanks,
Fuad
@Fuad,
This formula will return the last word in a string of text…
=TRIM(RIGHT(SUBSTITUTE(A1,” “,REPT(” “,99)),99))
Hi Rick
That is an excellent piece of code. I had searched the net for something similar and all I got was complex huge lines of codes. Thanks for that masterpiece, could you also pls explain how Substitute and REPT function are working in this example for me to understand it. Thx
@Fuad,
Sorry, I meant to include the following before I sent my reply…
As structured, the formula works for text of 99 characters or less… if your text can be longer, simply change both occurences of 99 to a number that is larger than your longest string of text.
Thanks Rick,
That helps a lot
Regards/Fuad
This formula will return the last word in a string of text…
=TRIM(RIGHT(SUBSTITUTE(A1,” “,REPT(” “,99)),99))
This works great! Is there any way to actually REMOVE the last name from the data in the cell. In other words, to split Tommy Lee Jones, into Tommy Lee in one field, and Jones in the next.
Thanks, and respond to [email protected]. THANK YOU VERY MUCH
@Stuart Krawl,
First off, IMHO, when you ask a question in a public forum, you should not ask to have the answer emailed to you… people who read your question may want to see the answer also. Now, for your question… no, you cannot modify the data in a cell using a formula… you would need to use a macro like this one…
Sub SeparateLastNameOut()Dim Cell As Range, NameParts() As String
On Error Resume Next
For Each Cell In Selection
NameParts = Split(Cell)
Cell.Offset(0, 1).Value = NameParts(UBound(NameParts))
NameParts(UBound(NameParts)) = “”
Cell.Value = Trim(Join(NameParts))
Next
On Error GoTo 0
End Sub
One note about identifying last names from text containing the full name… there is no foolproof method available to do it. I had a friend in work whose name was Joe Della Rossa… “Della Rossa” was his last name. The code above, the formula I posted previously and pretty much anyone else’s attempt to identify this last name from within text containing his full name will fail.
Thanks Rick, that’s great advice for Stuart. He can also subscribe to the comments for any article, and get an email if a new comment is posted. That’s better than posting your email address publicly.
Chip Pearson’s (CP) formula works just fine for me until I get to the names with 2nd and 3rd names. Any ideas how I can fix names like these?
Doe, John Smith F.
Doe, John Smith Richard F.
Doe, Ma. Jane Dianne F.
Here’s how they currently look like using CP’s formula:
Names are in Column A
Column B/Column C/ Column D
Doe/John/Smith F.
Doe/John/Smith Richard F.
Doe/Ma./Jane Dianne F.
Here’s what it should look like:
Doe/John Smith/F.
Doe/John Smith Richard/F.
Doe/Ma. Jane Dianne/F.
I tried working the formulas out but the complexity of it just boggles my mind.
Any help from you guys? 🙂
Debra your tutorials are awesome! I just need a little more help. I have a sheet in wich I have multiple strings on one cell separated by a coma and other more groups down in the same column B, I need them to be copied to another sheet and I already did all that, I have the code wich splits and copies but there are sometimes cells in blank in the same column. I just need to organize the data so it shows in the other sheet:
Column B | ColumnC | ColumnD | ColumnE | Column F
Group 1 | | Group 2 | | Group 3
my code:
Sub SplitTest()
Dim arrText() As String
Dim varItm As Variant
Dim rngText As Range
Dim rngCl As Range
Dim i As Integer
Dim j As Integer
Set rngText = Sheet8.Range(“B1000:B” & Range(“B” & Rows.count).End(xlUp).row)
j = -4
For Each rngCl In rngText
arrText = Split(rngCl, “,”)
i = 2
For Each varItm In arrText ‘????
Cells(i, j) = varItm
i = i + 1
Next varItm
j = j + 1
Next rngCl
End Sub
Thanks
Thank you! You saved me hours of work.
Ihave a spreadsheet with first and middle name in a cell with no space i.e. AnnaVictoria or JanetMay or DavidAnthony. Does any one know how I can split this into first name and second name in separate cells. I have been trawling for hours and cannot find anything. I need relatively easy steps to follow if possible. Many thanks in advance.
Hi Please tel me the VBA code which will print the part of the sender name exported from outlook to excel
For eg : sender name will be name1,name2 (name3).I want only name 2 to be exported to excel as a output.Kindly help
Hi, “Stuart Krawll” thank you very much for the easy function, it helped alot.
Just to add to your post, i used for the first name split.
=TRIM(LEFT(SUBSTITUTE(A1,” “,REPT(” “,99)),99))
i want to extract last name from string but i am unable kindly help.
ex:
KOSHALYA KAJAL D/O POONAM SUTHAR – i want SUTHAR
KEVARAM PATEL S/O MEGHAJI PATEL – i want PATEL
HOW CAN APPLY FORMULA?
for each group of names – I have the first and last name in separate columns for the most part. I do see some of the group of names have the first name in the last name column and the last name in the first name column. The worksheet has over 200,000 grouped names – I am looking for a way to highlight the groups of names where the first name is in the last name column.
How to separate full name into 3 cols
I want to separate Sanjay S. Jadhav into three cols
I’m having a similar problem
have a cel that exported from exchange with a bunch of names
like this
lastname,first name ;
want to swap first name with last name but not in the email address
how do I do it?
I have a splitting question. I have a cell that is populated with:
Lastname, Firstname
How can I reverse that so it shows: Firstname Lastname within one cell?
If the name is in cell A2, use this formula in another cell:
=RIGHT(A2,LEN(A2)-FIND(“,”,A2,1)-1)
& ” ” &LEFT(A2,FIND(“,”,A2,1)-1)