使用VBA从具有后缀的范围中提取姓氏

Question

我在一个列中有一个全名列表，例如：

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

名称在一列中。缩写并不仅限于这些。

我想使用一个泛型函数来提取姓氏。有可能吗？

Answer 1

考虑以下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

​

​

备注：

您必须扩展此行：

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

以包含您希望排除的所有其他初始集。

当您发现其他异常时，您还必须更新此代码来处理它们！

Answer 2

删除标点符号，拆分成一个数组，然后向后移动，直到找到一个字符串，该字符串与可以忽略的绰号"ii/jr/sr/dr“的查找结果不匹配。

您还可以添加一个检查，以根据标记的长度消除它们。

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