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

by Allen Wyatt
(last updated September 9, 2017)

15

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

Using a Progress Indicator in Macros

A few tips on adding a progress indicator that runs during long macro calculations.

Discover More

AutoFilling with the Alphabet

If you need to fill a number of cells with a specific sequence of characters (such as the alphabet), there are several ...

Discover More

Delaying in a Macro

At times, you may want your macro to pause execution. This tip presents four different ways you can add a delay into your ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Adding a Missing Closing Bracket

When working with large amounts of data, it is a good idea to make sure that the data all consistently follows a pattern. ...

Discover More

Calculating a Geometric Standard Deviation

One of the areas in which Excel provides worksheet functions is in the arena of statistical analysis. You may want to ...

Discover More

Relative References within Named Ranges

Excel is usually more flexible in what you can reference in formulas than is immediately apparent. This tip examines some ...

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}] 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 four more than 2?

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.