Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Separating Names into Individual Columns.

Separating Names into Individual Columns

Written by Allen Wyatt (last updated March 16, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016


23

Lance has a set of names in a workbook. The names are all in column A, and some have first and last name, while others use first, middle, and last names. He needs to separate the names into individual columns, but the Text to Columns wizard doesn't provide satisfactory results. It does the separation OK, but the two-vs.-three names issue means that Lance need to do a lot of manual massaging of the data once it is split up.

The solution to the problem is to not rely on the Text to Columns wizard, but instead use a number of formulas to get the names into columns. The results you achieve still depend, in large part, on the condition of the data you are parsing. If your data is in the format "first middle last" (with the middle name being optional), then you can use the following formula to pull out the first name:

=LEFT(A1,SEARCH(" ",A1,1)-1)

This formula checks for the first space in the name, and then assumes that everything before that space is the first name. The next formula is used to determine if there is a middle name, and if there is, display it:

=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) -
SEARCH(" ", A1, 1)), 1)) = TRUE, "", LEFT(
RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)),
SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ",
A1, 1)), 1) - 1))

Even though the formula shows as 5 lines here, it is really a single formula and should be entered as such. The formula checks for the existence of a second space in the name. If an error is generated (there is no second space) then the formula returns nothing; there is no middle name. If a second space is detected (there is no error generated), then the formula returns everything from after the first space up through the second space.

The next formula is used to extract the last name:

=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))

This formula relies on the results that were returned for both the first and middle names. It returns everything left in the original name after accounting for the length of the first name (assumed in cell B1) and the middle name (assumed in C1). (See Figure 1.)

Figure 1. Separating names into columns.

Again, these formulas work if the names in column A follow some pretty strict guidelines. The names must be in the "first last" or "first middle last" format. If there are qualifiers in the name such as Ms., Dr., Jr., or III, then you won't get satisfactory results. In addition, if the last name contains a space (as in "John van Kamp" or "Mary Anne St. Marie") or if the name contains no spaces at all, then you also won't get satisfactory results.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12383) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Separating Names into Individual Columns.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Ensuring Consistent Lines on Each Page

Need to have a specific number of lines on each page in your documents? What if those documents are subdocuments to a ...

Discover More

Automatically Adding Tabs in Footnotes

When you add a footnote to a document, Word's normal formatting adds a space after the footnote number and before the ...

Discover More

Sorting ZIP Codes

Sorting ZIP Codes can be painless, provided all the codes are formatted the same. Here's how to do the sorting if you ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Relative References to Cells in Other Workbooks

When you construct a formula and click on a cell in a different workbook, an absolute reference to that cell is placed in ...

Discover More

Incrementing Numeric Portions of Serial Numbers

If you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric ...

Discover More

Deriving a Secant and Cosecant

Two rather common trigonometric functions are secants and cosecants. Excel doesn't provide functions to calculate these, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2021-06-25 14:35:29

Gary Donohue

Dear Allen,

I just subscribed to your letter due to your help on this page https://excelribbon.tips.net/T012383_Separating_Names_into_Individual_Columns.html.

All I can say is BRILLIANT and I would pay for the newsletter.

Gary Donohue Bloomington ILLINOIS


2021-03-30 16:30:24

Yvan Loranger

Here's another way:

Type =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,SUBSTITUTE(A1," "," "),A1) in column B; for just 1st & last name people it adds a 2nd " " between 1st & last name so middle name occupies a column > this & everybody enjoys 3 columns after you run TextToColumn.

I use LibreOffice [successor to OpenOffice] instead of MS-Office, your mileage may vary.


2021-03-16 18:49:43

Alec W

This is great until you hit certain different name structures, particularly Dutch.
For example, imagine a list containing:
John Smith
Jennifer Jane Jones
Frida van Dam
Jan van den Berg

Been doing my head in for years..


2020-12-01 09:46:33

Crystal Strader

Hi, Allen. I 'm hoping you can help me with the worst case scenario of names list. Here's an example:
Harvey R Schatz MD
Michael Richardson MD
Dr. Samit Patel MD
Monique Desarmeaux DO
Kang Lee Tu MD
Leon Jackson Jr MD
You can see my challenge. Any workable suggestions for me?

Many thanks,
Crystal


2019-08-22 11:45:32

Peter Atherton

Salisu
The easiest way is to use Text To Columns in the Data Menu. Select your name and choose Data --> Text To Columns. Use a Space as the delimeter and keep pressing Next until it is done.
The Title will also be split and you will have to join it again with a formula. Such as A2&" "& B2. The T Macro below will also do this as long as the data is not too big (It needs revision)
The figure below might help

(see Figure 1 below)

Figure 1. 


2019-08-20 22:08:44

Salisu Abdullahi

How can someone separate this names Dr. Marry Patrice Franklin (Dr. Marry as a First Name, Patrice as a Middle Name, Franklin as a Last Name)


2019-06-13 00:17:11

Matt

Similar to the article "Separating Names into Individual Columns" any tips of how to set up a formula to separate names into individual columns when you have multiple names in a cell with the last name first? Example, (Miller, Ann Marie & Barry, John).


2019-06-09 15:34:48

Ali Ali Mohsin

Hi. this formula works fine. However, how can one get multiple middle names into one column as middle names

Lets say full name consisted of 4 names assumably (Martha Luther Arther Mcdonalds)

what formula can be made to get the 2 names between first and last (Luther and Arther) in one column as middle name.

Regards


2018-01-23 11:24:52

Peter Atherton

@Micky Avidan

Thank's Micky, I hadn't thought to change it to a value and check the result. Excellent stuff!


2018-01-23 03:27:12

Michael (Micky) Avidan

@Peter Atherton,
In order to eliminate the extra Space at the end of the result - I would suggest to subtract 1 from the result of the last FIND function
=IFERROR(MID(A23,FIND(" ",A23)+1,FIND(" ",A23,FIND(" ",A23)+1)-FIND(" ",A23)-1),"")
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL


2018-01-22 11:04:47

Peter Atherton

The following is a bit shorter for the middle name

=IFERROR(MID(A23,FIND(" ",A23)+1,FIND(" ",A23,FIND(" ",A23)+1)-FIND(" ",A23)),"")


2018-01-22 03:04:08

Alan Elston

EDIT: I mennt to say....
That trick of “expanding the space” to a big one, taking out up to the number of the expansion or a bit less, then trimming off the excess space is one that has caught my eye a few times. I often thought it could make a novel way to do some sort of secret selective filtering..I have not thought too much yet on exactly how..


2018-01-22 03:02:30

Alan Elston

@ Michael (Micky) Avidan
Thanks for adding that Micky
That trick of “expanding the space” to a big one, taking out up to the number of the expansion or a bit less, then trimming off the excess space is one that has caught my eye a few times. I have not thought too much yet on exactly how..

@ Peter
The one line Evaluate Range thing has intrigued me for some time. I hope to make some summary posts on that soon. Its potential is not realised in my opinion as it can be a bit difficult to understand how it works. I think I am getting close to fully understand it. Some of the little tricks you sometimes need to do are not straight forward.

Alan Elston


2018-01-21 08:58:32

Peter Atherton

Hi Alan

Thanks for your reply, I had seen the previous post on reordering names and was impressed with all the comments. I copied Nick's formula and two copies of the Reorder macros, by Nick and Willy, into my file of Miscellaneous Macros. I've also copied your list of sites relating to one line macros - i've been too daunted to try before.

Regards
Peter


2018-01-20 05:19:25

Michael (Micky) Avidan

@To whom it may concern:
The to extract the last name can be a bit shorter:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL


2018-01-20 02:48:36

Alan Elston

Hi Peter
Thanks for sharing all that. By the way there is a similar Blog from Allen for the simpler two name case. We did some shorter Evaluate Range alternative approaches there.
https://excelribbon.tips.net/T012042_Reordering_Last_Name_and_First_Name.html
That came out of another Allen Blog
https://excelribbon.tips.net/T010768_Shortening_ZIP_Codes.html
I expect it would be quite a challenge to do a version of an Evaluate Range that would do as much as your code!
Alan


2018-01-19 11:02:57

Peter Atherton

Morton

A couple of names where entered incorrectly with the previous macro. I spent ages trying to incorporate generations into the other macro. If there are a lot of names like this then this macro will do the trick.

Sub SplitGenerations()
Dim c As Range
Dim i As Integer, x As Variant

For Each c In Selection
x = Split(c, " ")
On Error Resume Next
For i = UBound(x) To LBound(x) + 1 Step -1
If IsNumeric(x(i) * 1) Or LCase(x(i)) = "jnr." Then
x(i - 1) = x(i - 1) & " " & CStr(x(i))
x(i) = ""
c.Offset(0, 2) = x(LBound(x))
c.Offset(0, 4) = x(1)
GoTo skip
End If
Next
skip:
Next c
End Sub

Of course, it would be great if some one could successfully combine the two!

(see Figure 1 below)


Figure 1. 




2018-01-19 10:37:25

Peter Atherton

Morton

It is a bit of a pain to split names when they include Titles (Mr. Mrs. etc) or letters after the name or a name includes honorifics 'du Pre'. The only way is to cover each thing individually. Here is my effort.

Option Explicit
Sub t()
Dim x As Variant, i As Integer, j As Integer, h As Integer, k As Integer
Dim Title() As Variant, vaLCase() As Variant
Dim wordCount As Integer, wdsRemaining As Integer
Dim bTitle As Boolean, bHons As Boolean, bGen As Boolean, str As String, str2 As String
Dim c As Range, MyOffset As Integer, honCount As Integer

Title = Array("Dr.", "Frau", "Fraulin", "Herr.", "Mr.", "Mrs.", "Ms.", "Prof.", "Sir")
' Array contains terms that should be lower case
vaLCase = Array("der", "du", "van", "von")

For Each c In Selection
str = "": str2 = ""
bTitle = False: bHons = False: bGen = False
honCount = 0

If Len(c) = 0 Then Exit Sub

wordCount = Len(c) - Len(WorksheetFunction.Substitute(c, " ", "")) + 1
x = Split(c, " ")
'Two names must be first and last names so..
If wordCount - honCount = 2 Then
c.Offset(0, 2) = x(LBound(x))
c.Offset(0, 4) = x(UBound(x))
GoTo skip
End If

'check for Letters after name OK!
honCount = 0: bHons = False
For i = UBound(x) To LBound(x) Step -1
If IsNumeric(x(UBound(x) * 1)) Then Exit For

On Error Resume Next
Do While x(i) = UCase(x(i))
str = x(i) & " " & str
honCount = honCount + 1
c.Offset(0, 5).Value = str
i = i - 1
If Len(str) > 0 Then bHons = True
Loop
Exit For
Next i

str = ""

' Find title, if any
bTitle = False

' check title list for match with first item in x
' OK!
For j = LBound(Title) To UBound(Title)
If Title(j) = x(LBound(x)) Then
c.Offset(0, 1).Value = x(LBound(x))
bTitle = True
Exit For
End If
Next j

str = ""

' Now we enter the names - How many are left?
wdsRemaining = wordCount - honCount


' find the Last names with honorific _
"der", "du", "van", "von"
str = x(UBound(x))
str2 = x(UBound(x) - 1)

For k = LBound(vaLCase) To UBound(vaLCase)
bHons = False
If str2 = vaLCase(k) Then
bHons = True
If bHons Then
c.Offset(0, 4).Value = str2 & " " & str
x(UBound(x)) = str2 & " " & str
honCount = honCount + 2
x(UBound(x) - 1) = ""
Exit For
End If
End If
Next k

'honCount = honCount + 1
'Find remaining name(s) (1 to 3)
wdsRemaining = wordCount - honCount
If bTitle Then
i = 1
wdsRemaining = wdsRemaining - 1
Else: i = 0
End If

If wdsRemaining = 1 And bTitle Then
c.Offset(0, 2) = x(i)
GoTo skip
ElseIf wdsRemaining = 1 And bTitle = False Then
c.Offset(0, 2) = x(i)
GoTo skip
ElseIf wdsRemaining = 2 And bTitle = False _
And bHons = False Then
c.Offset(0, 2) = x(0)
c.Offset(0, 4) = x(1)
GoTo skip
ElseIf wdsRemaining = 2 And bTitle Then
c.Offset(0, 2) = x(1)
c.Offset(0, 4) = x(2)
GoTo skip
ElseIf wdsRemaining = 2 And bTitle = False _
And bHons Then
MyOffset = 2
For k = i To wdsRemaining
If i = wdsRemaining Then GoTo skip
c.Offset(0, MyOffset) = x(i)
i = i + 1
MyOffset = MyOffset + 1
Next k
ElseIf wdsRemaining = 3 And bTitle = False Then
MyOffset = 2
For k = i To wdsRemaining
' If i = wdsRemaining Then GoTo skip
c.Offset(0, MyOffset) = x(i)
i = i + 1
MyOffset = MyOffset + 1
Next k
ElseIf wdsRemaining = 3 And bTitle Then
MyOffset = 2
For k = i To wdsRemaining
' If i = wdsRemaining Then GoTo skip
c.Offset(0, MyOffset) = x(i)
i = i + 1
MyOffset = MyOffset + 1
Next k

End If


skip:

Next c
End Sub

(see Figure 1 below)


Figure 1. 




2018-01-05 10:41:11

Morton Wakeland

The middle name formula works perfect for simplistic name as you show above in your example of names. However if the name has a title attached to it, like John Jones, M.D. - what is returned is Jones,
It appears the addition of that comma is messing things up.
If I enter just John Jones - of course I get nothing, meaning, correctly, there is no middle name.

Have an addition to the middle name formula to account for a no middle name with appended title?
Thank you Allen!
Mort


2018-01-05 09:34:12

Morton Wakeland

Great formula for last name Allen - I'm almost there. However, what if there is a qualifier after the last name, e.g., John Jones, M.D.
Using your last name formula I get Jones, M.D. So, how does one SUBTRACT all text to the right of the first and only comma? This is most likely very, very simple but working with text strings has always baffled me. Thanks so much for any suggestions.
Mort


2017-09-11 07:21:36

Syed Sabihur Rahman

Actually, Text to Columns worked just fine for me when I tried to solve this problem. The First and Last names actually showed up in adjacent columns unlike the blank column which shows up in this case.

In cases where there are no Middle names, a simple BLANK search for the third column will suffice to know whether the word displayed in the second column is the Middle Name or the Last name.


2017-09-11 05:04:00

David Robinson

Me, I think I'd use helper columns to store the locations of each space character found, then use these in conjunction with MID. I think this'd be easier if there are names with two middle names, for example.

For surnames with "van", "von", "de", "jr", "III" etc., you could do a series of nested substitutions before finding the last space. These substitutions would remove the space between the "van" etc. and the rest of the surname, so you'd replace " van " (spaces both sides) with " van" (no space after) for each surname prefix, and you'd replace " jr" (space before) with "jr" (no space before) for each surname suffix. Do this and then find the last space, and then use MID to use this location as the start point, but use MID on the original string so you return the surname with all the spaces still there. Use MID, not RIGHT, because MID will count from the left, and therefore those space characters you removed won't mess anything up because they were from after that position.

Incidentally, to be on the safe side (because it's conceivable that the letters "jr" might be the start of someone's middle name or surname), it'd be safer to append a termination character (e.g. \, or even just a space) to the end of the name being tested, and then you can look for " jr\".


2017-09-10 17:26:43

Alec Whatmough

I too had Lance's issue when using Excel to manipulate employee data for nearly 5000 people. Eventually, I woke up to using formulae to extract first and last names instead of TTC, as per this tip.
Of course, there were issues with surnames like "Van Gogh", Van de Berg", "Le Roux" etc.
My solution, rather than trying to write the nested IF formula from hell, was to use conditional formatting to show any cells that contained " Van ", " Le ", " St" etc, then fixed those manually. This may not be so effective in parts of Europe, though, so I'd love to see a more fool-proof method!


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.