如何在不使用 VBA 的情况下在 Excel 中执行反向字符串搜索? [英] 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," ","")))))

如果您的原始字符串可以包含管道|"字符，然后用其他一些不会出现在您的源中的字符替换上面的两个字符.(我怀疑 Brad 的原文被破坏了，因为翻译中删除了一个不可打印的字符).

奖励:它是如何工作的(从右到左):

LEN(A1)-LEN(SUBSTITUTE(A1," ","")) –原始字符串中的空格数
SUBSTITUTE(A1," ","|", ... ) –仅用 |
替换 final 空格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," ",""))))))

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

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