如何在Excel中执行反向字符串搜索而不使用VBA？ [英] How can I perform a reverse string search in Excel without using VBA?

问题描述

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

使用内置的Excel函数（无VBA），是否有一种方法隔离每个字符串中的最后一个字？

示例：

 
作为人？ - >人类
负面，我是一个肉冰棍 - >冰棒
阿齐兹！光！ - >光！

解决方案

这个被测试并且工作（基于Brad的原始帖子）：

  = RIGHT（A1，LEN（A1）-FIND（|，SUBSTITUTE（A1，，|，
 LEN（A1）-LEN（SUBSTITUTE（A1，，）））））
  

如果您的原始字符串可能包含管道|字符，然后在上面替换两个不会出现在您的源中的其他字符。 （我怀疑布拉德的原件被破坏，因为翻译中没有打印的字符）。

奖金：工作原理（从右到左）：

LEN（A1）-LEN（SUBSTITUTE（A1，，））–原始字符串中的空格数

SUBSTITUTE（A1，，|，...）–仅使用 |

FIND（|，...）替换最终 ）–找到替换的绝对位置 | （这是最后的空格）

右（A1，LEN（A1） - ...））–返回之后的所有字符 |

编辑：源文本不包含空格，将以下内容添加到公式的开头：

  = IF（ISERROR（FIND（， A1）），A1，...）
  

现在制作整个公式：

  = IF（ISERROR（FIND（，A1）），A1，RIGHT（A1，LEN（A1） 
 SUBSTITUTE（A1，，|，LEN（A1）-LEN（SUBSTITUTE（A1，，））））））
  pre> 
 
 
或者您可以使用其他的 = IF（COUNTIF（A1，* *）语法
 
 
 
当原始字符串可能包含最后位置的空格时，在计算所有空格时添加一个修剪函数：使函数如下：
  = IF（ISERROR（FIND（，B2）），B2，RIGHT（B2，LEN（B2） 
 SUBSTITUTE（B2，，|，LEN（TRIM（B2）） -  LEN（SUBSTITUTE（B2，，）））））））
  
 
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!

解决方案
This one is tested and does work (based on Brad's original post):
=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.

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," ",""))))))


                        
这篇关于如何在Excel中执行反向字符串搜索而不使用VBA？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！