如何在不使用 VBA 的情况下在 Excel 中执行反向字符串搜索？
我有一个包含字符串列表的 Excel 电子表格。 每个字符串由若干单词组成，但每个字符串中的单词数量不同。
使用内置 Excel 函数（无 VBA），有没有办法隔离每个字符串中的最后一个单词？
例子：
Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
Aziz! Light! -> Light!I have an Excel spreadsheet containing a list of strings. Each string is made up of several words, but the number of words in each string is different.
Using built in Excel functions (no VBA), is there a way to isolate the last word in each string?
Examples:
Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
Aziz! Light! -> Light!如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（16）
这个已经过测试并且确实有效（基于 Brad 的原始帖子）：
如果您的原始字符串可能包含管道“|” 字符，然后将上面的两个字符替换为源中不会出现的其他字符。 （我怀疑布拉德的原件已损坏，因为翻译中删除了不可打印的字符）。
附赠：它是如何工作的（从右到左）：
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))– 原始字符串中的空格计数SUBSTITUTE(A1," ","|", ... )– 仅用|替换最后空格FIND("|", ... )– 查找被替换的|的绝对位置（即最后一个空格）Right(A1,LEN(A1) - ... ))– 返回|之后的所有字符EDIT: 以考虑以下情况源文本不包含空格，请将以下内容添加到公式的开头：
现在制作整个公式：
或者您可以使用
=IF(COUNTIF(A1,"* *")语法 ：当原始字符串的最后一个位置可能包含空格时，请添加一个修剪函数，同时计算所有空格：使函数如下
This one is tested and does work (based on Brad's original post):
If your original strings could contain a pipe "|" character, then replace both in the above with some other character that won't appear in your source. (I suspect Brad's original was broken because an unprintable character was removed in the translation).
Bonus: How it works (from right to left):
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))– Count of spaces in the original stringSUBSTITUTE(A1," ","|", ... )– Replaces just the final space with a|FIND("|", ... )– Finds the absolute position of that replaced|(that was the final space)Right(A1,LEN(A1) - ... ))– Returns all characters after that|EDIT: to account for the case where the source text contains no spaces, add the following to the beginning of the formula:
making the entire formula now:
Or you can use the
=IF(COUNTIF(A1,"* *")syntax of the other version.When the original string might contain a space at the last position add a trim function while counting all the spaces: Making the function the following:
这是我非常成功地使用的技术：
要获取字符串中的第一个单词，只需从 RIGHT 更改为 LEFT
另外，将 A1 替换为包含文本的单元格。
This is the technique I've used with great success:
To get the first word in a string, just change from RIGHT to LEFT
Also, replace A1 by the cell holding the text.
Jerry 的答案的一个更强大的版本：
无论字符串的长度、前导或尾随空格或其他什么，它都有效，而且它仍然非常短和简单。
A more robust version of Jerry's answer:
That works regardless of the length of the string, leading or trailing spaces, or whatever else and it's still pretty short and simple.
我发现这个 在 google 上，在 Excel 2003 & 中测试 它对我有用：
[编辑]我没有足够的代表来发表评论，所以这似乎是最好的地方...BradC的答案也不适用于尾随空格或空单元格...
[第二次编辑]实际上，它也不适用于单个单词......
I found this on google, tested in Excel 2003 & it works for me:
[edit] I don't have enough rep to comment, so this seems the best place...BradC's answer also doesn't work with trailing spaces or empty cells...
[2nd edit] actually, it doesn't work for single words either...
新答案2022年9月28日
考虑新的excel函数： TEXTAFTER（检查可用性）您可以通过一个简单的公式来实现：
New answer 9/28/2022
Considering the new excel function: TEXTAFTER (check availability) you can achieve it with a simple formula:
这是非常强大的——它适用于没有空格、前导/尾随空格、多个空格、多个前导/尾随空格的句子...并且我使用 char(7) 作为分隔符而不是竖线“|” 以防万一这是所需的文本项目。
This is very robust--it works for sentences with no spaces, leading/trailing spaces, multiple spaces, multiple leading/trailing spaces... and I used char(7) for the delimiter rather than the vertical bar "|" just in case that is a desired text item.
这是非常干净和紧凑的，而且效果很好。
它不会因为没有空格或一个单词而出现错误陷阱，但这很容易添加。
编辑：
这可以处理尾随空格、单个单词和空单元格场景。 我还没有找到打破它的方法。
This is very clean and compact, and works well.
It does not error trap for no spaces or one word, but that's easy to add.
Edit:
This handles trailing spaces, single word, and empty cell scenarios. I have not found a way to break it.
要添加到 Jerry 和 Joe 的答案中，如果您想查找最后一个单词之前的文本，可以使用：
A1 中的“My Little cat”将导致“My Little”（其中 Joe 和 Jerry 的会给出“cat”）
就像杰瑞和乔隔离最后一个单词一样，这只会得到该单词左边的所有内容（然后将其修剪回来）
To add to Jerry and Joe's answers, if you're wanting to find the text BEFORE the last word you can use:
With 'My little cat' in A1 would result in 'My little' (where Joe and Jerry's would give 'cat'
In the same way that Jerry and Joe isolate the last word, this then just gets everything to the left of that (then trims it back)
复制到一列中，选择该列并选择 HOME > 编辑> 查找& 选择、替换：
全部替换。
星号后面有一个空格。
Copy into a column, select that column and HOME > Editing > Find & Select, Replace:
Replace All.
There is a space after the asterisk.
想象一下字符串可以颠倒过来。 那么这真的很容易。 而不是处理字符串：
您在 A2 中使用
With
=LEFT(A1;FIND(" ";A1)-1)，您会得到"My"with (1 ) 和"tac"与 (2)，它颠倒了"cat"，即 (1) 中的最后一个单词。有一些 VBA 可以反转字符串。 我更喜欢公共 VBA 函数
ReverseString。按照描述安装上述内容。 然后，将您的字符串放在 A1 中，例如
"My little cat"并将此函数放在 A2 中：您将在 A2 中看到
"cat"。上面的方法假设单词之间用空格分隔。
IF子句适用于包含单个单词的单元格 = 单元格中没有空格。 注意：TRIM和CLEAN原始字符串也很有用。 原则上，它从 A1 反转整个字符串，并简单地找到反转字符串中最后一个（反转）单词（即"tac "）旁边的第一个空白。LEFT选择该单词，另一个字符串反转重新构成该单词的原始顺序（“cat”）。FIND语句末尾的-1删除空格。这个想法是，使用
LEFT和FIND第一个空格可以轻松提取字符串中的第一个（！）单词。 然而，对于最后一个（！）单词，当您尝试这样做时，RIGHT函数是错误的选择，因为不幸的是 FIND 没有您想要分析字符串的方向的标志。因此整个字符串被简单地反转。
LEFT和FIND工作正常，但提取的字符串是相反的。 但一旦你知道如何反转一根弦，他的动作就没什么大不了的了。 公式中的第一个ReverseString语句完成此工作。Imagine the string could be reversed. Then it is really easy. Instead of working on the string:
you work with
With
=LEFT(A1;FIND(" ";A1)-1)in A2 you get"My"with (1) and"tac"with (2), which is reversed"cat", the last word in (1).There are a few VBAs around to reverse a string. I prefer the public VBA function
ReverseString.Install the above as described. Then with your string in A1, e.g.,
"My little cat"and this function in A2:you'll see
"cat"in A2.The method above assumes that words are separated by blanks. The
IFclause is for cells containing single words = no blanks in cell. Note:TRIMandCLEANthe original string are useful as well. In principle it reverses the whole string from A1 and simply finds the first blank in the reversed string which is next to the last (reversed) word (i.e.,"tac ").LEFTpicks this word and another string reversal reconstitutes the original order of the word (" cat"). The-1at the end of theFINDstatement removes the blank.The idea is that it is easy to extract the first(!) word in a string with
LEFTandFINDing the first blank. However, for the last(!) word theRIGHTfunction is the wrong choice when you try to do that because unfortunately FIND does not have a flag for the direction you want to analyse your string.Therefore the whole string is simply reversed.
LEFTandFINDwork as normal but the extracted string is reversed. But his is no big deal once you know how to reverse a string. The firstReverseStringstatement in the formula does this job.您也可以通过反转字符串并找到第一个空格来实现此目的
=MID(C3,2+LEN(C3)-SEARCH(" ",CONCAT(MID(C3,SEQUENCE(LEN(C3),,LEN( C3),-1),1))),LEN(A1))CONCAT(MID(C3,SEQUENCE(LEN(C3),,LEN(C3),-1), 1))SEARCH(" ",...=MID(C3,2+LEN(C3)-SEARCH...You can achieve this also by reversing the string and finding the first space
=MID(C3,2+LEN(C3)-SEARCH(" ",CONCAT(MID(C3,SEQUENCE(LEN(C3),,LEN(C3),-1),1))),LEN(A1))CONCAT(MID(C3,SEQUENCE(LEN(C3),,LEN(C3),-1),1))SEARCH(" ",...=MID(C3,2+LEN(C3)-SEARCH...我翻译成 PT-BR，因为我也需要这个。
（请注意，我已将空格更改为
\因为我只需要路径字符串的文件名。）I translated to PT-BR, as I needed this as well.
(Please note that I've changed the space to
\because I needed the filename only of path strings.)实现此目的的另一种方法如下
Another way to achieve this is as below
我也有一个这样的任务，当我完成后，使用上面的方法，我想到了一个新方法：为什么不这样做：
这听起来怎么样？
I also had a task like this and when I was done, using the above method, a new method occured to me: Why don't you do this:
How does this sound?