excel-formula - 如何使用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(... 但它返回第一个数字的索引，而不是最后一个。

Answer 1

作为起点:如果我们知道最后一个数字的位置，我们可以使用LEFT使字符串到达​​该点。假设位置是5:

=LEFT(A1, 5)

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

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

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

=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)))

但是，数字可以出现多次。因此，我们需要一种方法来查找字符串中值的 last 出现(因为 FIND 和 SEARCH 默认情况下会返回 first 出现)。

The SUBSTITUTE function有 3 个强制参数 - 初始字符串、要替换的值、要替换的值 - 和一个可选参数 - 要替换的事件。通常，这会被省略，以便替换 所有 次出现。但是，如果我们知道一个字符在字符串中出现多少次，那么我们可以将 just last 实例替换为特殊/不常见的 sub -要搜索的字符串。

要计算一个字符在字符串中出现的次数，只需从字符串的长度开始，然后在 SUBSTITUTE 时减去长度即可。该角色的所有副本都没有:

=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)))