我有一个包含字符串列表的 Excel 电子表格，每个字符串由多个单词组成，但每个字符串中单词数量不同。
是否有一种方法可以在不使用 VBA 的情况下利用内置的 Excel 函数来分离每个字符串中的最后一个单词?
示例：
Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
Aziz! Light! -> Light!如何在Excel中进行反向字符串搜索而不使用VBA？
190
- eJames
11
4我想知道为什么您设置了无法使用VBA的人工限制？ - EBGreen
3我可以用VBA轻松解决它，但我想知道是否有非VBA的解决方案。对于大数据集，VBA往往会带来性能损失。 - e.James
很棒的问题。非常优秀的答案。非常优雅。 - Mike Parks
1我想说的是，使用公式而不是VBA的原因是因为你可以保持 .xslx 格式。一旦你编写了一行VBA代码，你就变成了一个.xlsm文件，并且可能会对任何COM端口、注册表进行写入操作，打开应用程序等等——VBA已经整合到Windows中，可以被任何病毒检测器、电子邮件客户端或尝试打开它的应用程序所使用。因此，留在“电子表格领域”对于普通的“安全意识用户”来说增加了很多价值。这就是为什么Lambda函数如此强大，它们让你更接近VBA，而不会使你的文档成为安全威胁。 - Mike Parks
1通过函数，您只是以“优雅”的方式处理了数据 - 但是使用VBA，您可以收获“旋风”或创造“天堂”。选择哪一个：取决于您。有时，“保持简单”是最优雅的解决方案。 - Mike Parks
显示剩余6条评论
16个回答
234
这个经过测试并且有效（基于 Brad 的原始帖子）：
如果您的原始字符串中可能包含管道字符"|", 那么请将上面两个字符都替换为其他在源中不会出现的字符。(我怀疑Brad原来的代码出了问题是因为在翻译时删除了一个不可打印的字符)。
奖励部分：如何运作（从右到左）：
=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
如果您的原始字符串中可能包含管道字符"|", 那么请将上面两个字符都替换为其他在源中不会出现的字符。(我怀疑Brad原来的代码出了问题是因为在翻译时删除了一个不可打印的字符)。
奖励部分：如何运作（从右到左）：
LEN(A1)-LEN(SUBSTITUTE(A1," ","")) - 原始字符串中空格的数量SUBSTITUTE(A1," ","|", ... ) - 仅将最后一个空格替换为|FIND("|", ... ) - 查找替换掉的|（也就是最后一个空格）的绝对位置Right(A1,LEN(A1) - ... )) - 返回该|之后的所有字符
编辑: 要考虑源文本不包含空格的情况，请在公式开头添加以下内容:=IF(ISERROR(FIND(" ",A1)),A1, ... )
现在制作整个公式：
=IF(ISERROR(FIND(" ",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",
SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))
或者您可以使用其他版本的=IF(COUNTIF(A1,"* *")语法。
当原始字符串可能在最后一个位置包含空格时，在计算所有空格时添加一个修剪函数：使函数如下：
=IF(ISERROR(FIND(" ",B2)),B2, RIGHT(B2,LEN(B2) - FIND("|",
SUBSTITUTE(B2," ","|",LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))))))
- BradC
8
1无法感谢你的足够。我还需要字符串的其余部分，所以只需将=RIGHT更改为=LEFT并删除LEN（A1）-即可获得其余部分。但是非常感谢您做了所有最初的工作 :) - Luke Duddridge
4+1：我得记住“LEN（A1）-LEN（SUBSTITUTE（A1，” “，”“））”这个技巧，以获取字符实例的数量。 - anschauung
2优秀的解决方案！唯一的问题是Excel本地化函数名称，因此您需要为非英语版本的Excel重新编写公式。这是俄语版本的重新编写变体：=ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК("|";ПОДСТАВИТЬ(A1;" ";"|";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))) - Michael Pliskin
此答案无法处理包含连续空格的字符串。 - somewhatsapient
1我建议将公式分解成临时列（稍后可以隐藏它们）。这对于调试特殊情况非常有用。 - wisbucky
显示剩余3条评论
94
这是我一直以来取得巨大成功的技巧：
=TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))
要获取字符串中的第一个单词，只需将RIGHT更改为LEFT
=TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))
同时，将A1替换为包含文本的单元格。
- Jerry Beaucaire
4
2对我来说可以 - 我只是用我的分隔符替换了第一个“ ”。如果我没记错，这两个100似乎将其限制为100个字符的字符串。 - Benjineer
2如果我们使用不同的分隔符，我们也需要在最后将其去除，例如
=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A1, ".", REPT(".", 100)), 100), ".", ""))。 - dumbledad9非常聪明的方法。它的作用是将每个空格替换为100个空格，然后返回最后100个字符而没有前导/尾随空格。 - Zenadix
@Benjineer 我认为它适用于长度超过100个字符的字符串。100个字符限制了单个单词的大小。如果您取正确（或左）的100个字符......如果一个单词长达101个字符，您会遇到问题，但是如果整体字符串为100个字符......带有3个空格......将填充到大约400个字符的字符串并仍然有效。 致Jerry Beaucaire-非常优雅，谢谢。 - Tin Bum
29
Jerry的回答有一个更加强大的版本：
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1))))
这个方法适用于任意长度的字符串，包括前后空格和其他情况，并且非常简短易懂。
- Joe Finkle
5
3这是一个非常好的公式。我将公式修改为
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), ".", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1))))，以获取文件扩展名。 - Adarsh Madrecha2再加一个
SUBSTITUTE 就可以让它适用于任何字符，而不仅仅是空格。=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(TRIM(A1),"/",REPT("/",LEN(TRIM(A1)))),LEN(TRIM(A1))),"/",""))，其中 "/" 是分隔符。 - PProteus@Joefinkle，这是一个非常简洁和聪明的解决方案。它运行得很好，只是要提到，如果分隔符是字符串，在某些情况下它不起作用，这里有一个分隔符的例子：
" ; "（由空格包围的分号），输入值为："Technology Sprint 24 ; Sprint 4"，它返回："; Sprint 4"。我正在使用@PProteus改编的解决方案。 - David Leal如果分隔符是字符串，可能的解决方法是在所有地方之前将其替换为某个特殊字符，例如在@PProteus公式“TRIM（A1）”中用以下内容替换：“TRIM（SUBSTITUTE（A1，“strDelimeter”，“;”））”，以便具有新的字符分隔符：“;”或者更好的方法是使用char（）函数来查找一些真正意外的字符。 - David Leal
2@PProteus 在添加第二个
SUBSTITUTE 以获得单字符解决方案的一般情况后，您不需要使用 TRIM 函数。该公式提供了预期的结果，消除了这部分内容：=SUBSTITUTE(RIGHT(SUBSTITUTE(TRIM(A1),";",REPT(";",LEN(TRIM(A1)))),LEN(TRIM(A1))),";","")。 - David Leal13
我在谷歌上找到了这个，在 Excel 2003 中测试过，对我有效：
=IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))))),A1)
[编辑]我没有足够的声望来进行评论，所以这似乎是最好的地方...BradC的答案也不能处理尾随空格或空单元格...
[第二次编辑]实际上，它也无法处理单个单词...
- Jon
2
目前最接近解决方案，但我会加入一个Trim()，因为尾随空格会破坏它。 - EBGreen
真的，可能最容易在一个中间单元格中使用trim()函数，然后将上述公式应用到中间单元格中。
此外，如果单元格恰好是空的，则会输出0，因此还可以使用isblank()函数进行包装。 - Jon
5
- David Leal
1
1谢谢！我不知道这个功能存在。 - Jonathan
3
=RIGHT(A1,LEN(A1)-FIND("`*`",SUBSTITUTE(A1," ","`*`",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
- Brad
4
你测试过了吗？对我来说，使用“您被归类为人类吗？”无法工作。 - Ed Guiness
不，它不起作用。但其中有一些有趣的元素。
LEN(A1)-LEN(SUBSTITUTE(A1," ","")) 可以给出字符串中空格的数量。 - BradC
那是一个有趣的函数。太遗憾它不起作用。我喜欢这个技巧，可以计算空格的数量。 - e.James
@Brad：我编辑了你的帖子，以显示*字符。原始内容未能正确打印它们。有了这些，它可以正常工作。+1 - e.James
3
这很干净简洁，功能良好。
{=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(1:999),1)=" ",ROW(1:999),0)))}
它没有为没有空格或一个单词的情况进行错误捕获，但这很容易添加。
编辑：
它处理了尾随空格、单个单词和空单元格的情况。我还没有找到打破它的方法。
{=RIGHT(TRIM(A1),LEN(TRIM(A1))-MAX(IF(MID(TRIM(A1),ROW($1:$999),1)=" ",ROW($1:$999),0)))}
- gabrielu
1
很棒。这是一个非常有趣的数组函数用法！我没想到在这么长的时间后还能得到另一个答案。谢谢你的分享。 - e.James
3
=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(7),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(7),
LEN(TRIM(A1))-LEN(SUBSTITUTE(" "&TRIM(A1)," ",""))+1))+1)
这个功能非常强大，它可以处理没有空格、前导/尾随空格、多个空格、多个前导/尾随空格的句子……我使用 char(7) 作为分隔符，而不是竖线“|”，以防竖线是所需的文本项。
- Mark Main
1
在我的Excel 2010中可以正常工作。谢谢。 - Rudiger Wolf
2
将以下内容复制到一列中，选择该列并进入“开始”菜单 > “编辑” > “查找和替换”：
全部替换。
星号后面有一个空格。
- pnuts
1
请注意，在Excel搜索中，*是一个（贪婪的）通配符，因此它匹配到最后一个空格并将其替换为空。 - Superfly Jon
2
除了Jerry和Joe的答案外，如果你想要找到最后一个单词之前的文本，可以使用：
=TRIM(LEFT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))))-LEN(TRIM(A1))))
如果A1单元格中填入'My little cat'，结果将是'My little'（而Joe和Jerry的则会得到'cat'）
与Jerry和Joe隔离最后一个词的方式相同，这个公式得到的是该词左侧的所有内容（然后再将其修整）。
- Andrew B
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接