如果不使用VBA，如何在Excel中执行反向字符串搜索?

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.

我有一个包含字符串列表的Excel电子表格。每个字符串由几个单词组成，但是每个字符串中的单词数是不同的。

Using built in Excel functions (no VBA), is there a way to isolate the last word in each string?

使用内置的Excel函数(没有VBA)，是否有方法隔离每个字符串中的最后一个单词?

Examples:

例子:

  Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
                  Aziz! Light! -> Light!

14 个解决方案

#1

185

This one is tested and does work (based on Brad's original post):

这个测试和做的工作(基于Brad的原创文章):

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",
     LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

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 string
SUBSTITUTE(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:

编辑:对于源文本不包含空格的情况，在公式的开头添加以下内容:

=IF(ISERROR(FIND(" ",A1)),A1, ... )

making the entire formula now:

现在制定整个公式:

=IF(ISERROR(FIND(" ",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",
    SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Or you can use the =IF(COUNTIF(A1,"* *") syntax of the other version.

或者您可以使用另一个版本的=IF(COUNTIF(A1，“* *”)语法。

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:

当原始字符串可能包含最后一个位置的空格时，在计算所有空格的同时添加一个修饰函数:

=IF(ISERROR(FIND(" ",B2)),B2, RIGHT(B2,LEN(B2) - FIND("|",
    SUBSTITUTE(B2," ","|",LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))))))

#2

This is the technique I've used with great success:

这就是我成功地运用的技巧:

=TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

To get the first word in a string, just change from RIGHT to LEFT

要获取字符串中的第一个单词，只需从右到左进行更改

=TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

Also, replace A1 by the cell holding the text.

同样，将A1替换为保存文本的单元格。

#3

A more robust version of Jerry's answer:

杰瑞的回答更加有力:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1))))

That works regardless of the length of the string, leading or trailing spaces, or whatever else and it's still pretty short and simple.

不管字符串的长度，前导或尾随空格，或者其他什么，它都可以工作，而且它仍然非常短和简单。

#4

I found this on google, tested in Excel 2003 & it works for me:

我在谷歌上发现了这个，在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)

[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...

我没有足够的代表来评论，所以这似乎是最好的地方……BradC的答案也不适用于尾随空格或空单元。实际上，它对单个词也不起作用……

#5

=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(7),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(7),
LEN(TRIM(A1))-LEN(SUBSTITUTE(" "&TRIM(A1)," ",""))+1))+1)

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.

这是非常健壮的——它适用于没有空格、前导/尾空格、多个空格、多个前导/尾空格的句子……我使用char(7)作为分隔符，而不是竖线“|”，以备需要的文本项。

#6

This is very clean and compact, and works well.

这是非常干净和紧凑，并且工作良好。

{=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(1:999),1)=" ",ROW(1:999),0)))}

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.

编辑:它处理尾随空格、单字和空单元格场景。我还没有找到打破它的方法。

{=RIGHT(TRIM(A1),LEN(TRIM(A1))-MAX(IF(MID(TRIM(A1),ROW($1:$999),1)=" ",ROW($1:$999),0)))}

#7

=RIGHT(A1,LEN(A1)-FIND("`*`",SUBSTITUTE(A1," ","`*`",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

#8

Imagine the string could be reversed. Then it is really easy. Instead of working on the string:

假设弦可以反转。那真的很简单。而不是在字符串上工作:

"My little cat" (1)

you work with

和你一起工作

"tac elttil yM" (2)

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).

与=LEFT(A1;FIND(";A1)-1)在A2中，你会得到"My" With (1) and "tac" With(2)，也就是(1)中的最后一个词"cat"。

There are a few VBAs around to reverse a string. I prefer the public VBA function ReverseString.

有一些VBAs可以反转一个字符串。我更喜欢公共VBA函数反向字符串。

Install the above as described. Then with your string in A1, e.g., "My little cat" and this function in A2:

安装上述描述。然后你的字符串在A1中，例如“我的小猫”，这个函数在A2中:

=ReverseString(LEFT(ReverseString(A1);IF(ISERROR(FIND(" ";A1));
  LEN(A1);(FIND(" ";ReverseString(A1))-1))))

you'll see "cat" in A2.

你会在A2中看到“cat”。

The method above assumes that words are separated by blanks. The IF clause is for cells containing single words = no blanks in cell. Note: TRIM and CLEAN the 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 "). LEFT picks this word and another string reversal reconstitutes the original order of the word (" cat"). The -1 at the end of the FIND statement removes the blank.

上面的方法假设单词由空格分开。IF子句用于包含单个单词的单元格=单元格中没有空格。注意:修剪和清洁的原始字符串也是有用的。原则上，它将整个字符串从A1中翻转过来，然后简单地在倒转的字符串中找到第一个空格，这是在最后一个(反)单词的旁边。“tac”)。左边选择这个单词，另一个字符串反转重新构成单词(“cat”)的原始顺序。查找语句末尾的-1将删除空白。

The idea is that it is easy to extract the first(!) word in a string with LEFT and FINDing the first blank. However, for the last(!) word the RIGHT function 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.

其思想是很容易用左字符串提取第一个(!)单词并找到第一个空格。然而，对于最后一个(!)字，当您尝试这样做时，正确的函数是错误的选择，因为不幸的是FIND没有您想要分析您的字符串的方向的标志。

Therefore the whole string is simply reversed. LEFT and FIND work as normal but the extracted string is reversed. But his is no big deal once you know how to reverse a string. The first ReverseString statement in the formula does this job.

因此整个字符串是反向的。左查找正常的工作，但提取的字符串是反向的。但一旦你知道如何扭转弦，他的就没什么大不了的。公式中的第一个ReverseString语句完成了这项工作。

#9

To add to Jerry and Joe's answers, if you're wanting to find the text BEFORE the last word you can use:

为了补充杰里和乔的答案，如果你想在最后一个词之前找到文本，你可以这样做:

=TRIM(LEFT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))))-LEN(TRIM(A1))))

With 'My little cat' in A1 would result in 'My little' (where Joe and Jerry's would give 'cat'

A1中有“我的小猫”，就会出现“我的小猫”(乔和杰瑞会在A1中画“猫”)

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)

就像杰里和乔把最后一个词分离出来一样，这只会把所有东西都放到左边(然后再把它整理回来)

#10

=LEFT(A1,FIND(IF(
 ISERROR(
  FIND("_",A1)
 ),A1,RIGHT(A1,
  LEN(A1)-FIND("~",
   SUBSTITUTE(A1,"_","~",
    LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))
   )
  )
 )
),A1,1)-2)

#11

I translated to PT-BR, as I needed this as well.

我翻译成了PT-BR，因为我也需要这个。

(Please note that I've changed the space to \ because I needed the filename only of path strings.)

(请注意，我已经将空间更改为\，因为我只需要路径字符串的文件名。)

=SE(ÉERRO(PROCURAR("\",A1)),A1,DIREITA(A1,NÚM.CARACT(A1)-PROCURAR("|", SUBSTITUIR(A1,"\","|",NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1,"\",""))))))

#12

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:

我也有一个这样的任务，当我完成时，使用上面的方法，我想到了一个新方法:为什么不这样做呢:

Reverse the string ("string one" becomes "eno gnirts").
反转字符串(“string one”变为“eno gnirts”)。
Use the good old Find (which is hardcoded for left-to-right).
使用好的旧查找(从左到右硬编码)。
Reverse it into readable string again.
再把它转换成可读字符串。

How does this sound?

这听起来如何?

#13

Another way to achieve this is as below

另一种实现这一点的方法如下

=IF(ISERROR(TRIM(MID(TRIM(D14),SEARCH("|",SUBSTITUTE(TRIM(D14)," ","|",LEN(TRIM(D14))-LEN(SUBSTITUTE(TRIM(D14)," ","")))),LEN(TRIM(D14))))),TRIM(D14),TRIM(MID(TRIM(D14),SEARCH("|",SUBSTITUTE(TRIM(D14)," ","|",LEN(TRIM(D14))-LEN(SUBSTITUTE(TRIM(D14)," ","")))),LEN(TRIM(D14)))))

#14

Copy into a column, select that column and HOME > Editing > Find & Select, Replace:

复制到列中，选择该列，编辑>查找和选择，替换:

Replace All.

替换所有。

There is a space after the asterisk.

星号后面有一个空格。

#1

185