如何使用Excel公式查找字符串中的最后一个数字

Question

我正在解析 Excel 中的字符串，并且需要返回最后一个数字之前的所有内容。例如：

Input: A00XX
Output: A00

在我的情况下，我知道最后一个数字将位于索引3和5之间，因此我正在使用暴力破解：

=LEFT([@Point],
IF(SUM((MID([@Point],5,1)={"0","1","2","3","4","5","6","7","8","9"})+0),5,
    IF(SUM((MID([@Point],4,1)={"0","1","2","3","4","5","6","7","8","9"})+0),4,
        IF(SUM((MID([@Point],3,1)={"0","1","2","3","4","5","6","7","8","9"})+0),3,
))))

不幸的是，我遇到了一些边缘情况，导致数字超过了索引5。是否有一种通用的方法来使用Excel公式查找字符串中的最后一个数字？

注意： 我尝试了=MAX(SEARCH(...但它返回的是第一个数字的索引，而不是最后一个。

- Dave Thunes

---

为什么不使用VBA方法？ - CoolMind

@CoolMind，这个电子表格将提供给其他（年长）人使用。他们过去曾经有使用带宏的工作簿的问题。把这个做成VBA函数很简单，但我希望避免这种情况发生。 - Dave Thunes

Answer 1

首先，如果我们知道最后一个数字的位置，我们可以使用LEFT函数获取该位置之前的字符串。假设该位置为5：

=LEFT(A1, 5)

但是，我们不知道最后一个数字的位置。现在，如果唯一有效的数字是0，并且只出现一次：那么我们可以使用 FIND 来定位该数字的位置：

=LEFT(A1, FIND(0, A1))

但是，我们有多个有效数字。假设我们拥有从0到9的所有数字，但是每个数字只能出现一次-然后我们可以在FIND数组上使用MAX来告诉我们哪个数字是最后一个:

=LEFT(A1, MAX(FIND({0,1,2,3,4,5,6,7,8,9}, A1)))

不幸的是，FIND 会抛出一个 #VALUE! 错误，如果没有找到任何数字，这会使 MAX 返回相同的错误。所以，我们需要使用 IFERROR 来解决这个问题：

=LEFT(A1, MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}, A1), 0)))

然而，数字可能会出现多次。因此，我们需要一种方法来查找字符串中值的最后一个出现位置（因为FIND和SEARCH默认返回第一个出现位置）。 SUBSTITUTE函数有3个必需参数——初始字符串、要替换的值和要替换为的值，以及一个可选参数——要替换的次数。通常，这被省略，以便替换所有出现。但是，如果我们知道一个字符在一个字符串中出现了几次，那么我们就可以用一个特殊/不常见的子字符串来替换仅仅最后一个实例。

要计算一个字符在一个字符串中出现的次数，只需从字符串的长度开始，然后用Nothing替换该字符的所有副本并减去其长度即可：

=LEN(A1) - LEN(SUBSTITUTE(A1, 0, ""))

这意味着我们现在可以将字符的最后一个出现替换为例如">¦<"，然后FIND 它：

=FIND(">¦<", SUBSTITUTE(A1, 0, ">¦<", LEN(A1) - LEN(SUBSTITUTE(A1, 0, ""))))

当然，我们希望对从0到9的所有数字执行此操作，并获取 MAX 值（记得使用 IFERROR ），因此我们需要将值数组放回去：

=MAX(IFERROR(FIND(">¦<", SUBSTITUTE(A1, {0,1,2,3,4,5,6,7,8,9}, ">¦<", LEN(A1) - LEN(SUBSTITUTE(A1, {0,1,2,3,4,5,6,7,8,9}, "")))), 0))

然后，我们将所有这些内容重新插入到我们最初的LEFT函数中：

=LEFT(A1, MAX(IFERROR(FIND(">¦<", SUBSTITUTE(A1, {0,1,2,3,4,5,6,7,8,9}, ">¦<", LEN(A1) - LEN(SUBSTITUTE(A1, {0,1,2,3,4,5,6,7,8,9}, "")))), 0)))

- Chronocidal

Answer 2

假设您要处理的字符串长度永远不会超过9个字符（根据您的描述，这似乎是一个安全的假设），以下是另一种解决方法：

=LEFT(A1,MATCH(0,0+ISERR(0+MID(A1,{1;2;3;4;5;6;7;8;9},1))))

根据你使用的Excel版本，这可能需要使用CTRL+SHIFT+ENTER进行提交。

注意，在数组常量{1;2;3;4;5;6;7;8;9}中，分隔符为分号，对于Excel的英语版本而言，分号表示行分隔符。如果你使用的是非英语版本，则可能需要进行修改。

当然，我们可以用动态构建来替换这个静态常量。但是，既然我们已经假设9是相关字符串的字符数上限，那么似乎并不需要这样做。

- Jos Woolley

---

我喜欢这个！非常简单。我正在使用Excel 365，所以我使用了Sequence而不是手动创建数组：=LEFT(A3,MATCH(0,0+ISERR(0+MID(A3,SEQUENCE(LEN(A3)),1)))) - Dave Thunes

虽然对于我的目的来说无关紧要，但是你的公式不适用于不连续的数字。例如，A00X0X返回的是A00而不是A00X0。 - Dave Thunes

我最终使用了 SEQUENCE 来允许任意长度，XMATCH 来查找最后一个数字，并使用 LET 进行简化。我真的很喜欢 Excel 365。这是我使用的公式：=LET(s, A3, IFERROR(LEFT(s, XMATCH(0, --ISERR(--MID(s, SEQUENCE(LEN(s)), 1)), 0, -1)),"")) - Dave Thunes

Answer 3

如果你拥有最新版本的Excel，可以尝试以下方法：

=LEFT(D1,
LET(x, SEQUENCE(LEN(D1)),
MAX(IF(ISNUMBER(NUMBERVALUE(MID(D1, SEQUENCE(LEN(D1)), 1))), x))))

例如：。

- EDS

---

这就是我想做的 - 谢谢！这是您的公式，稍微整理了一下：=LET(s, A2, x, SEQUENCE(LEN(s)), LEFT(s, MAX(IF(ISNUMBER(NUMBERVALUE(MID(s, x, 1))), x))))。 - Dave Thunes