1

I have a list of full names in a column like for example:

Dave M. Butterworth
Dave M. Butterworth,II
H.F. Light jr
H.F. Light ,jr.
H.F. Light sr 
Halle plumerey

The names are in a column. The initials are not limited to these only.

I want to extract the last name using a generic function. Is it possible?

Community
  • 1
  • 1
Sahil Oberoi
  • 29
  • 1
  • 6

2 Answers2

0

Consider the following UDF:

Public Function LastName(sIn As String) As String
  Dim Ka As Long, t As String
  ary = Split(sIn, " ")
  Ka = UBound(ary)
  t = ary(Ka)

  If t = "jr" Or t = ",jr" Or t = "sr" Or t = ",jr." Then
    Ka = Ka - 1
  End If

  t = ary(Ka)
  If InStr(1, t, ",") = 0 Then
    LastName = t
    Exit Function
  End If

  bry = Split(t, ",")
  LastName = bry(LBound(bry))
End Function

enter image description here

NOTE:

You will have to expand this line:

If t = "jr" Or t = ",jr" Or t = "sr" Or t = ",jr." Then

to include all other initial sets you wish to exclude.
You will also have to update this code to handle other exceptions as you find them !

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

Remove punctuation, split to an array and walk backwards until you find a string that does not match a lookup of ignorable monikers like "ii/jr/sr/dr".

You could also add a check to eliminate tokens based on their length.

Function LastName(name As String) As String
    Dim parts() As String, i As Long
    parts = Split(Trim$(Replace$(Replace$(name, ",", ""), ".", "")), " ")

    For i = UBound(parts) To 0 Step -1
        Select Case UCase$(parts(i))
            Case "", "JR", "SR", "DR", "I", "II"
            Case Else:
                LastName = parts(i)
                Exit Function
        End Select
    Next
End Function
Alex K.
  • 171,639
  • 30
  • 264
  • 288