Splitting names and surnames

[Rudi]

Hi Guys,

I have a long list of names in a single column similar to...
- Adams J. Stephen
- Twain Mark
- Van den Heever John
- Johnson Smith Kim
- Randall TM Harry
- etc...

Note that there are initials and double barrel surnames.

I have been using =LEFT(A1,SEARCH(" ",A1)-1) and =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) to pull out the surname and name but this does not spit it always in the right place. I figured that it will work better if I can search for the last space and pull out everything right of the last space as the name and everything left of the last space as the surname. I can try and figure this out for myself, but before I do, is there any advice or knowledge of a better method to do this??

TX

[Rudi]

Hi,

Here is my latest solution:
Name: =RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Surname: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

I would still like to know if there is a better way of doing this?

TX

[sdckapr]

I think the logic of your choice is not always true.

Adams J. Stephen

Is not:
Name: Stephen and Surname: Adams J. but I presume

Name: J. Stephen (First Initial and Middle name) and Surname: Adams
Though even
Name: Stephen J. (First and Middle Initial) and Surname: Adams could also be true


And that Randall TM Harry is name: TM Harry and surname Randall.
or even name: Harry TM and surname Randall.

If there are a lot you have to adjust, I would almost lean towards just Text to columns with a space, sorting the list by the number of columns and then manually fixing them in to the proper columns (Name, Middle1, Middle2, Sur1, Sur2, Sur3) and then once sorted out combine the Middle/Middle2 and the Surname parts to have 3 columns at the end. Once separated into these 3 it is easy to combine as desired later

Steve

[Rudi]

This is good advice Steve.

This is the essence of the thread. I was wanting to find out all the ways to do this (most effectively).

I can recall in the "old lounge"... way back... that there was some VBA code that did this very smartly with a loop (or something). It worked out the split between name and surname very accurately across many examples. I was not able to dig this up, but was hoping to maybe get an example of it based on the request in this post.

Cheers for the input!

[HansV]

You could write VBA code that adds a bit of "intelligence" - for example, you could treat the Afrikaans/Dutch prefixes such as "van", "van den" etc. separately. But it's almost impossible to get everything right: a computer program can't tell the difference between Pieterse Jan Karel (where "Pieterse" is the last name and "Jan Karel" a double first name) and Friese Greene Tim (where "Friese Greene" is a double last name and "Tim" the first name).

[Rudi]

I agree. This is where bits and bytes and human logic are separated.

TX

PS: I still have not been able to track down that VBA code on Woody's site. (Would you know if the servers there still carry the posts from a number of years back?)

[dasadler]

Rudi, I think that if you use the search box at the bottom of the forum (spreadsheets) it will look all the way back. However, if you use the search at the top (even the advanced search) that is only goes back a certain amount.

I believe all the search operands can be used in the bottom of forum search box.

[Rudi]

TX...

I will try that out when I am on that forum again.

[HansV]

Most old posts are still available on the Windows Secrets Lounge, as it's called nowadays, although some text and a sizeable percentage of the attachments got lost in the big crash of August 2007 and in the conversion to new software in 2009.

[HansV]

If you're OK with using VBA, here is a macro that handles prefixes of Afrikaans/Dutch and Huguenot/French names.

Code: Select all

Sub SplitNames()
  Dim intPos As Integer
  Dim oCell As Range
  Dim strTemp As String
  Dim strValue As String
  On Error GoTo Exit_Sub
  ' Look at one column only
  Selection.Columns(1).Select
  ' Replace two consecutive spaces with one
  Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, MatchCase:=False
  ' Replace two consecutive periods with one
  Selection.Replace What:="..", Replacement:="."
  ' Insert columns for result
  Selection.Columns("B:C").Insert xlShiftToRight
  For Each oCell In Selection
    strValue = oCell.Value
    ' Prefixes
    strTemp = ""
    intPos = 1
    If LCase$(Left$(strValue, 8)) = "van der " Then
      strTemp = "van der "
      intPos = 9
    ElseIf LCase$(Left$(strValue, 8)) = "van den " Then
      strTemp = "van den "
      intPos = 9
    ElseIf LCase$(Left$(strValue, 7)) = "van de " Then
      strTemp = "van de "
      intPos = 8
    ElseIf LCase$(Left$(strValue, 7)) = "van ‘t " Then
      strTemp = "van 't "
      intPos = 8
    ElseIf LCase$(Left$(strValue, 7)) = "van ’t " Then
      strTemp = "van 't "
      intPos = 8
    ElseIf LCase$(Left$(strValue, 7)) = "van 't " Then
      strTemp = "van 't "
      intPos = 8
    ElseIf LCase$(Left$(strValue, 6)) = "in 't " Then
      strTemp = "in 't "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 6)) = "in ‘t " Then
      strTemp = "in 't "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 6)) = "in ’t " Then
      strTemp = "in 't "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 6)) = "op 't " Then
      strTemp = "op 't "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 6)) = "op ‘t " Then
      strTemp = "op 't "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 6)) = "op ’t " Then
      strTemp = "op 't "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 3)) = "'t " Then
      strTemp = "'t "
      intPos = 4
    ElseIf LCase$(Left$(strValue, 3)) = "‘t " Then
      strTemp = "'t "
      intPos = 4
    ElseIf LCase$(Left$(strValue, 3)) = "’t " Then
      strTemp = "'t "
      intPos = 4
    ElseIf LCase$(Left$(strValue, 4)) = "van " Then
      strTemp = "van "
      intPos = 5
    ElseIf LCase$(Left$(strValue, 4)) = "ter " Then
      strTemp = "ter "
      intPos = 5
    ElseIf LCase$(Left$(strValue, 7)) = "op den " Then
      strTemp = "op den "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 6)) = "op de " Then
      strTemp = "op de "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 4)) = "ten " Then
      strTemp = "ten "
      intPos = 5
    ElseIf LCase$(Left$(strValue, 4)) = "den " Then
      strTemp = "den "
      intPos = 5
    ElseIf LCase$(Left$(strValue, 6)) = "de la " Then
      strTemp = "de la "
      intPos = 7
    ElseIf LCase$(Left$(strValue, 3)) = "de " Then
      strTemp = "de "
      intPos = 4
    ElseIf LCase$(Left$(strValue, 3)) = "da " Then
      strTemp = "da "
      intPos = 4
    ElseIf LCase$(Left$(strValue, 3)) = "du " Then
      strTemp = "du "
      intPos = 4
    ElseIf LCase$(Left$(strValue, 3)) = "te " Then
      strTemp = "te "
      intPos = 4
    ElseIf LCase$(Left$(strValue, 2)) = "l'" Then
      strTemp = "l'"
      intPos = 3
    ElseIf LCase$(Left$(strValue, 2)) = "l’" Then
      strTemp = "l'"
      intPos = 3
    ElseIf LCase$(Left$(strValue, 2)) = "l‘" Then
      strTemp = "l'"
      intPos = 3
    ElseIf LCase$(Left$(strValue, 2)) = "d'" Then
      strTemp = "d'"
      intPos = 3
    ElseIf LCase$(Left$(strValue, 2)) = "d’" Then
      strTemp = "d'"
      intPos = 3
    ElseIf LCase$(Left$(strValue, 2)) = "d‘" Then
      strTemp = "d'"
      intPos = 3
    End If
    strValue = Trim$(Mid$(strValue, intPos))
    ' Last Name
    intPos = InStr(strValue, " ")
    oCell.Offset(0, 1) = strTemp & Left$(strValue, intPos - 1)
    ' First name
    oCell.Offset(0, 2) = Mid$(strValue, intPos + 1)
  Next oCell
Exit_Sub:
  Set oCell = Nothing
End Sub

[Rudi]

Wow...

The only thing I am surprised about from your reply Hans, is that you did not use Select Case for that macro.

Tx a stack for the code! I always learn from it and enjoy stepping through it to see how the programmer thought throw it.

Cheers!!

[HansV]

This is a modified and much stripped down version of a macro I wrote ten years ago to clean up long lists of Dutch names (it also included the ability to split off honorifics and titles).
I didn't use Select Case because the strings I'm looking for are of different lengths. If they had all been of the same length, I could have used something like

Code: Select all

Select Case Left(strValue, 8)
  Case "van den "
    ...
  Case "van der "
    ...
etc.

But that was not possible here.