Separating Names into Individual Columns

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

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.

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

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

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)

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)

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).

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

@Micky Avidan

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

@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

The following is a bit shorter for the middle name

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

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

@ 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

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

@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

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

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)

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)

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

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

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.

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\".

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!