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

Question

有没有一种有效的方法来使用基本函数识别字符串中最后一个字符/字符串匹配？即不是字符串的最后一个字符/字符串，而是字符/字符串的最后出现位置在字符串中的位置。搜索和查找都是从左到右工作的，因此我无法想出如何应用它们而不需要冗长的递归算法。而且这个解决方案现在似乎已经过时。这个解决方案

- geotheory

---

因为我想要在字符串"one.two.three.four"中找到最后一个句点"."的位置。 - geotheory

有趣的是，对问题错误的理解却得到了点赞。 - geotheory

我认为应该从另一个角度来看待这个问题，这意味着其他人也没有理解你在问题中的意思，因此认为建议是正确的解决方案...即使你选择的答案也以“我想我明白你的意思”开始...这不是批评，而是请求努力使你的问题更容易理解，以帮助人们更轻松地回答。 - John Bustos

我本应该在问题中添加示例，但我认为已经有足够的内容可以区分这个问题与关于字符串最后一个字符的查询：search和find都是查询字符串内容的术语，“match”是一个标准术语，加上链接的示例。 - geotheory

可能是重复的问题：如何从Excel列中提取最后一个子字符串？ - Jean-François Corbett

稍微更新的跨站点重复问题：https://superuser.com/q/680769 - Mathieu K.

Answer 1

我想我明白你的意思。比如说，你想要以下字符串中最右边的\（该字符串存储在单元格A1中）：

Drive:\Folder\SubFolder\Filename.ext

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

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

这告诉我们最右边的\在第24个字符处。它通过查找"@"并将最后一个"\"替换为"@"来实现这一点。它使用以下方法确定最后一个：

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

在这种情况下，子字符串只是长度为1的"\"，因此您可以省略末尾的除法，直接使用：

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

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

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

这是不带末尾\的文件夹路径：

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

要仅获取文件名：

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

---

然而，如果您可以确保A1中的字符串不包含单词之间重复的空格†，那么这里有一个获取特定字符最后一次出现右侧所有内容的替代版本。因此，使用我们相同的示例，这也将返回文件名：

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

† 因为这是TRIM文档所述的内容（重点在于我）：

从文本中删除除单词之间的单个空格外的所有空格。对从其他应用程序接收到可能具有不规则间距的文本使用TRIM。

由于文件名可能包含重复的空格，因此使用基于TRIM的解决方案可能会导致将重复的空格替换为单个空格而得到不正确的结果。

- tigeravatar

---

头晕；头晕；头晕；“好的-不想在不理解的情况下使用它，所以...嗯，好的，嗯...等等，什么？哇！真的吗？太聪明了！我有信心如果不是几个小时或几周，我都想不到那个！”-加一分，遗憾的是，因为要自己完成+5或+10很困难 --- 如果你被卡住了，请参考以下说明：（使用此示例）用空替换所有（3）个“\”实例（将字符串长度缩短3个字符）->那个“\”（第3个）是最后一个；用某些唯一的字符替换它，并FIND该唯一字符的位置...真棒...谢谢！ - Code Jockey

很聪明。只需注意单元格中是否已经包含“@”，否则您需要使用其他内容进行替换。您可以使用=ISNUMBER(SEARCH("@",A1))进行检查，如@gwin003所建议的。 - geotheory

非常感谢您提供的提取最后一次出现位置右侧所有内容的选项，因为大多数时候我都在寻找“字符串y中字符串x的最后一次出现位置”，而我的最终目标实际上是获取该最后出现位置右侧的所有内容。 - SSilk

我使用了[CHAR(9)]（制表符）代替“@”，因为我的数据最初来自制表符分隔的文件，我保证它不会在我的数据中出现。 - Josiah Yoder

同一个答案的另一种解释请参见：https://superuser.com/a/680776 - Mathieu K.

Answer 2

你可以创建一个自定义函数并在公式中使用。VBA内置了一个名为InStrRev的函数，正好符合你的需求。

将以下代码放入新模块中：

Function RSearch(str As String, find As String)
    RSearch = InStrRev(str, find)
End Function

假设原始字符串在B1中，那么您的函数将如下所示：

=LEFT(B1,RSearch(B1,"\"))

- Keith Rust

---

这是一个非常简单但有效的解决方案。如果你的项目可以使用一些 VBA，就使用这个吧。 - Patrick Hofman

Answer 3

新答案 | 2022年3月31日：

随着越来越多的新功能，我们可以使用TEXTBEFORE()函数。该函数目前处于BETA测试阶段，但很快将在未来广泛使用。

=LEN(TEXTBEFORE(A2,B2,-1))+1

这里的技巧在于第三个参数告诉函数检索我们在第二个参数中给出的子字符串的最后一次出现。编写本函数时，默认情况下仍然区分大小写，这可以通过可选的第四个参数来处理。

---

原始回答 | 2020年6月17日：

随着Excel的更新，出现了新的函数和方法。尽管在旧版本中也可以复制，但当一个人使用Excel O365时，可以使用以下方法：

=MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),1)="Y"))

这也可以用于检索（重叠的）子字符串的最后位置：

=MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),2)="YY"))

---

| Value  | Pattern | Formula                                        | Position |
|--------|---------|------------------------------------------------|----------|
| XYYZ   | Y       | =MATCH(2,1/(MID(A2,SEQUENCE(LEN(A2)),1)="Y"))  | 3        |
| XYYYZ  | YY      | =MATCH(2,1/(MID(A3,SEQUENCE(LEN(A3)),2)="YY")) | 3        |
| XYYYYZ | YY      | =MATCH(2,1/(MID(A4,SEQUENCE(LEN(A4)),2)="YY")) | 4        |

---

虽然这样做既可以避免使用任意替换字符，也可以允许重叠的模式匹配，但“缺点”是需要使用数组。

---

注意：在旧版本的Excel中，您可以通过以下方式强制执行相同的行为

=MATCH(2,1/(MID(A2,ROW(A1:INDEX(A:A,LEN(A2))),1)="Y"))

通过使用CtrlShiftEnter输入，或者使用内联的INDEX来消除隐式交叉点：

=MATCH(2,INDEX(1/(MID(A2,ROW(A1:INDEX(A:A,LEN(A2))),1)="Y"),))

- JvdV

---

我认为现在继承了这个刻度，虽然全部的历史荣誉属于@tigeravatar长期的解决方案https://dev59.com/5GMl5IYBdhLWcg3wMUaP#18617720 - geotheory

注意：虽然我的建议在代码高尔夫比赛中可能会获胜，但是当大量使用数组时并不总是推荐的。然而，它确实具有其他好处，我已经试图明确提到了这一点。这使得@Tigeravatar的答案仍然同样相关！ - JvdV

成功了。谢谢！ - Steven Lee

Answer 4

tigeravatar和Jean-François Corbett建议使用这个公式来生成在最后一个“\”字符右侧的字符串。

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

如果使用空格作为分隔符，则公式必须更改为：

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

无需多言，"{"字符可以被替换为任何在处理文本中不会“正常”出现的字符。

- Eugen Sumindan

---

我认为这个解决方案更加优雅和易懂。此外，如果您将LEN（A1）乘以一个数字，您可以获得倒数第n个出现的位置，前提是您的原始字符串不包含空格（这是我的情况）。 - Zlatin Zlatev

Answer 5

我刚想出了这个解决方案，不需要使用 VBA；

在我的示例中查找最后一个 "_" 出现的位置；

=IFERROR(FIND(CHAR(1);SUBSTITUTE(A1;"_";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"_";"")));0)

从内到外解释；

SUBSTITUTE(A1;"_";"") => replace "_" by spaces
LEN( *above* ) => count the chars
LEN(A1)- *above*  => indicates amount of chars replaced (= occurrences of "_")
SUBSTITUTE(A1;"_";CHAR(1); *above* ) => replace the Nth occurence of "_" by CHAR(1) (Nth = amount of chars replaced = the last one)
FIND(CHAR(1); *above* ) => Find the CHAR(1), being the last (replaced) occurance of "_" in our case
IFERROR( *above* ;"0") => in case no chars were found, return "0"

希望这对您有所帮助。

- Mr Thee

---

这个可以运行，但是在“;0)”之前你漏了一个)。=IFERROR(FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))),0) - Lodlaiden

Answer 6

考虑到@SSilk 的评论中说“我的最终目标是获取该字符最后一次出现的右侧所有内容”，可以使用非常简单的公式来复制字符串列（例如A）并在副本（例如B列）上应用“查找和替换”的备选方法。例如，对于以下示例：Drive:\Folder\SubFolder\Filename.ext

此操作将返回所选择字符（此处为\）的最后一个实例后面的内容（此处为Filename.ext），这往往是我们想要的结果，而且还可以通过短小的公式方便地找到该字符的最后一个位置：

=FIND(B1,A1)-1

- pnuts

Answer 7

你可以使用我创建的函数来查找字符串中最后一个实例。

当然，接受的Excel公式可以工作，但它太难读和使用了。在某个时候，你必须分解成更小的块，以便于维护。我的下面的函数是可读的，但这与你使用命名参数在公式中调用它无关。这使得使用它变得简单。

Public Function FindLastCharOccurence(fromText As String, searchChar As String) As Integer
Dim lastOccur As Integer
lastOccur = -1
Dim i As Integer
i = 0
For i = Len(fromText) To 1 Step -1
    If Mid(fromText, i, 1) = searchChar Then
        lastOccur = i
        Exit For
    End If
Next i

FindLastCharOccurence = lastOccur
End Function

我这样使用它：

=RIGHT(A2, LEN(A2) - FindLastCharOccurence(A2, "\"))

- Michael Z.

Answer 8

在较新的 Excel 版本中（2013 及以上），闪填可能是一个简单快捷的解决方案，请参见：在 Excel 中使用闪填。

- RS Finance

Answer 9

对于字符串A1中的子字符串B1，请使用以下公式：

=XMATCH(B1,MID(A1,SEQUENCE(LEN(A1)),LEN(B1)),,-1)

从内向外操作，MID(A1,SEQUENCE(LEN(A1)),LEN(B1))将字符串A1拆分成一个动态数组，每个子字符串的长度均为B1的长度。为了找到子字符串B1的最后一次出现的位置，我们需要使用XMATCH，并将其Search_mode参数设置为-1。

- bkraines

Answer 10

我有点晚了，但也许这可以帮助。问题中的链接有一个类似的公式，但我的使用IF()语句来消除错误。

如果你不怕Ctrl+Shift+Enter，你可以用数组公式做得很好。

字符串（在单元格A1中）： "one.two.three.four"

公式：

{=MAX(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)))}  use Ctrl+Shift+Enter

结果：14

首先，

ROW($1:$99)

该函数返回一个由1到99的整数构成的数组：{1,2,3,4,...,98,99}。

接下来，

MID(A1,ROW($1:$99),1)

返回在目标字符串中找到的长度为1的字符串数组，然后在达到目标字符串长度后返回空字符串：{"o","n","e",".",..."u","r","","",""...}

接下来，

IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99))

将数组中的每个项与字符串“。”进行比较，并返回字符串中字符的索引或FALSE：{FALSE，FALSE，FALSE，4，FALSE，FALSE，FALSE，8，FALSE，FALSE，FALSE，FALSE，FALSE，14，FALSE，FALSE.....}

最后，

=MAX(IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99)))

返回数组的最大值：14

此公式的优点是简短易懂，不需要任何特殊字符。

缺点是需要使用 Ctrl+Shift+Enter，并且有字符串长度限制。可以通过下面的变体解决这个问题，但该变体使用 OFFSET() 函数，这是一种易受干扰（即：较慢）的函数。

不确定此公式的速度与其他公式相比如何。

变体：

=MAX((MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)=".")*ROW(OFFSET($A$1,,,LEN(A1)))) works the same way, but you don't have to worry about the length of the string

=SMALL(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2) determines the 2nd occurrence of the match

=LARGE(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2) determines the 2nd-to-last occurrence of the match

=MAX(IF(MID(I16,ROW($1:$99),2)=".t",ROW($1:$99))) matches a 2-character string **Make sure you change the last argument of the MID() function to the number of characters in the string you wish to match!

- Stadem