Excel：字符串中的最后一个字符/字符串匹配

问题描述：

是否有一种有效的方式来识别使用基本功能的字符串中的最后一个字符/字符串匹配？即不是字符串的最后一个字符/字符串，而是字符串中字符/字符串最后一次出现的位置。 搜索和 find 都从左到右工作，所以我无法想象如何应用没有冗长的递归算法。而此解决方案现在似乎已过时。

Is there an efficient way to identify the last character/string match in a string using base functions? I.e. not the last character/string of the string, but the position of a character/string's last occurrence in a string. Search and find both work left-to-right so I can't think how to apply without lengthy recursive algorithm. And this solution now seems obsolete.

答

我想我得到你的意思。比方说，你想要在以下字符串（存储在单元格A1中）中最右边的\

I think I get what you mean. Let's say for example you want the right-most \ in the following string (which is stored in cell A1):

驱动器：\Folder\SubFolder\ Filename.ext

Drive:\Folder\SubFolder\Filename.ext

要获得最后一个\的位置，您可以使用以下公式：

To get the position of the last \, you would use this formula:

=FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))

这告诉我们最右边的\在字符24.它通过寻找@来代替最后一个\与@。它使用

That tells us the right-most \ is at character 24. It does this by looking for "@" and substituting the very last "\" with an "@". It determines the last one by using

(len(string)-len(substitute(string, substring, "")))\len(substring)

在这种情况下，子字符串只是\长度为1，所以你最后可以离开分部，只需使用：

In this scenario, the substring is simply "\" which has a length of 1, so you could leave off the division at the end and just use:

=FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))

现在我们可以用它来获取文件夹路径：

Now we can use that to get the folder path:

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

这是没有拖尾的文件夹路径\

Here's the folder path without the trailing \

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)

只得到文件名：

=MID(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

然而，这里是一个替代版本，可以获取特定字符的最后一个实例右边的所有内容。所以使用我们相同的例子，这也会返回文件名：

However, here is an alternate version of getting everything to the right of the last instance of a specific character. So using our same example, this would also return the file name:

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

Excel：字符串中的最后一个字符/字符串匹配

相关推荐