从单元格中提取最后一个子字符串

Question

从单元格中提取最后一个子字符串

excelexcel-formulaexcel-2007

32

我有一列名字。我需要将该列中的姓氏分割出来并放入另一列。

姓氏在右侧由空格分隔。

单元格中的内容为 A2 = Alistair Stevens，我在单元格 B2 中输入了公式（我需要在单元格 B2 中得到 'Stevens'）

我尝试使用以下公式：

=RIGHT(A2,FIND(" ",A2,1)-1)

=RIGHT(A2,FIND(" ",A2))

这两个公式对于此单元格都有效，但当我向下填充/复制并粘贴到下面的单元格时，它不起作用。我得到了错误的值！

A3 -> David Mckenzie

B3 -> Mckenzie

- Shyam Natraj Kanagasabapathy

请发布A3的内容和生成的B3。 - Dr. belisarius

10个回答

6

@Jean提供的答案是可行的，但比较晦涩（尽管它没有处理尾随空格）

作为替代方案，请考虑使用vba用户定义函数（UDF）

Function RightWord(r As Range) As Variant
    Dim s As String
    s = Trim(r.Value)
    RightWord = Mid(s, InStrRev(s, " ") + 1)
End Function

Use in sheet as
=RightWord(A2)

- chris neilsen

7

难懂啊？等Excel支持在公式中添加注释再给我打电话吧。 - Jean-François Corbett

@Jean: 是啊，我也希望如此。这将使维护我的代码变得轻松得多。 - chris neilsen

2

更简单的方法是： =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99)) 如果您确定数据不包含任何不需要的空格，则可以使用A2替换TRIM(A2)。

基于Rick Rothstein解释的概念： http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String 对于我的回复晚了，我感到抱歉！

- shrivallabha.redij

1

如果您想在文本中获取倒数第二个单词，您可以将此宏作为函数在电子表格中使用：

Public Function Get2ndText(S As String) As String

Dim sArr() As String
Dim i As Integer
sArr = Split(S, " ")

'get the next to the last string
i = UBound(sArr) - 1
Get2ndText = sArr(i)

End Function

然后在您的电子表格B1单元格中输入以下文本：

CURRENT OWNER 915 BROADWAY ST HOUSTON TX 77012-2126

在B2单元格中，您的公式将是：

=Get2ndText(B1)

结果将会是：

TX

- Kerry White

感谢 Kerry White 提供的解决方案。点赞！ - SIM

1

在Excel中尝试此函数：

Public Shared Function SPLITTEXT(Text As String, SplitAt As String, ReturnZeroBasedIndex As Integer) As String
        Dim s() As String = Split(Text, SplitAt)
        If ReturnZeroBasedIndex <= s.Count - 1 Then
            Return s(ReturnZeroBasedIndex)
        Else
            Return ""
        End If
    End Function

你可以这样使用它：

名字 (A1) | 姓氏 (A2)

单元格A1中的值为Michael Zomparelli。

我想要在A2列中获得姓氏。

=SPLITTEXT(A1, " ", 1)

最后一个参数是你想要返回的从零开始的索引。因此，如果你在空格字符上拆分，则索引0 = Michael，索引1 = Zomparelli。

上述函数是一个 .Net 函数，但可以很容易地转换为 VBA。

- Michael Z.

1

现在有一个新的Excel公式可以很容易地实现这一点：

TEXTAFTER(A2," ",-1)

这将返回在最后一个空格之后出现的姓氏（从右向左搜索-1个实例）

对于文件名也非常有用，例如

=HYPERLINK(A2,TEXTAFTER(A2,"\",-1))

这将创建一个链接，可以轻松打开文件，同时在列A中只显示文件名，如果你有完整的路径。

- tnavidi

0

Right(A1, Len(A1)-Find("(asterisk)",Substitute(A1, "(space)","(asterisk)",Len(A1)-Len(Substitute(A1,"(space)", "(no space)")))))

试一下。希望能够运行。

- user46372819

-1

RIGHT函数返回第一个参数中从右边数起的第二个参数指定数量的字符。因此，您需要将列A的总长度减去索引。

=RIGHT(A2, LEN(A2)-FIND(" ", A2, 1))

你应该考虑在出现的所有地方使用TRIM(A2)...

- Pok

2

当存在名字、中间名和姓氏时，此方法无法正常工作。例如，如果有一个名为“John Steven Smith”的人，那么得到的结果将是“Steven Smith”。 - Shyam Natraj Kanagasabapathy

3

他没有这样做。但他要求“最后一个子字符串”。 - Dr. belisarius

@belisarius -- 啊，是的，“从右侧用空格分隔”。这很困难--FIND从左侧开始。如果字符串中嵌入了未知数量的空格，他可能会运气不佳。 - Jay Elston

@Jay，我没有回答是因为我不明白他为什么在B3中得到了“Formula”。 - Dr. belisarius

@belisarius：需要用公式填写单元格B3，以获取姓氏作为结果。单元格B3的内容不是“公式”。对于误解我深感抱歉。 - Shyam Natraj Kanagasabapathy

-1

试试这个：

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(" ",TRIM(A2)))

我能够复制/粘贴公式，它可以正常工作。

这里是Excel文本函数列表（在2011年5月有效，但可能会在微软下次更改网站时失效）。 :-(

如果您需要处理中间名或缩写、头衔等信息，可以使用多级嵌套IF()函数。Excel公式不支持循环，因此您的操作受到一定限制。

- Jay Elston

3

与@Pok的答案类似，这并不是提取最后一个子串，而是删除第一个子串。当只有名字和姓氏时，这种方法很好用，但是当存在一个或多个中间名时就不行了。 - Jean-François Corbett

-1

试试这个：

Right(RC[-1],Len(RC[-1])-InStrRev(RC[-1]," "))

- Ronald Davis

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jean-François Corbett · Accepted Answer

这个可以实现，即使有中间名字：

=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))

如果你想要的不是最后一个名字，可以查看这个答案。

如果你的名字中有尾随空格，那么你可能想通过在上述公式中用TRIM(A2)替换所有A2来去除它们。

请注意，你第一个公式=RIGHT(A2,FIND(" ",A2,1)-1)之所以能在Alistair Stevens这个例子中“工作”，只是因为"Alistair"和" Stevens"恰好包含相同数量的字符（如果计算" Stevens"前导空格的话）。