excel - 如何在VBA中提取第二个空格后的文本

Question

示例值:Becky G Smith

我已经可以获取 A 列中每个人的名字，并将结果放入 B 列:

For i = 2 To lastrow
x = InStr(1, Cells(i, "A").Value, " ")
y = InStr(1, Cells(i, "A").Value, "@")

If InStr(1, Cells(i, "A").Value, " ") > 0 Then
Cells(i, "B").Value = Left(Cells(i, "A"), x - 1)
ElseIf InStr(1, Cells(i, "A").Value, "@") > 0 Then
Cells(i, "B").Value = Left(Cells(i, "A"), y - 1)
End If

Next i

ElseIf InStr(1, Cells(i, "A").Value, "@") > 0 Then声明之所以存在，是因为有时我要处理电子邮件，例如 Becky@gmail.com

问题在于将“Smith”作为她的姓氏。我不想要中间的首字母。我已经尝试过这样获取姓氏:

For i = 2 To lastrow
w = InStr(1, Cells(i, "A").Value, " ")
x = InStr(w, Cells(i, "A").Value, " ")
y = InStr(1, Cells(i, "A").Value, "@")
Z = Len(Cells(i, "A").Value)

If InStr(1, Cells(i, "A").Value, " ") > 0 Then
Cells(i, "C").Value = Right(Cells(i, "A"), Z - x)
ElseIf InStr(1, Cells(i, "A").Value, "@") > 0 Then
Cells(i, "C").Value = Right(Cells(i, "A"), Z - y)
End If

Next i

但最终w在x = InStr(w, Cells(i, "A").Value, " ")带来了一个错误。显然VBA考虑w等于 0。所以我需要一种提取第二个空格后的文本的方法。

Answer 1

试试这个:

Public Function GetLastName(sName As String) As String

    Dim aWords()    As String

    aWords = Split(sName, " ")

    GetLastName = aWords(UBound(aWords))

End Function

您可以在工作表中使用它