时间戳

首页 > 解决方案 > 如何使用excel公式找到字符串中的最后一个数字

excel-formula - 如何使用excel公式找到字符串中的最后一个数字

问题描述

我在 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(...，但它返回第一个数字的索引，而不是最后一个。

标签： excel-formula

解决方案

作为一个起点：如果我们知道最后一个数字的位置，我们可以使用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)))

但是，数字可以出现多次。因此，我们需要一种方法来查找字符串中最后一次出现的值（因为FIND并且SEARCH默认情况下会返回第一次出现的值）。

该SUBSTITUTE函数有 3 个强制参数——初始字符串、要替换的值、要替换的值——和一个可选参数——要替换的出现。通常，这会被省略，因此所有出现的地方都会被替换。但是，如果我们知道一个字符在一个字符串中出现了多少次，那么我们可以将最后一个实例替换为一个特殊/不常见的子字符串来搜索。

要计算一个字符在字符串中出现的次数，只需从字符串的长度开始，然后减去该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)))

推荐阅读