Excel公式技巧:同时定位字符串中的第一个和最后一个数字

在许多情况下，我们面临着确定字符串中第一个和最后一个数字的位置的问题，这可能是为了提取这两个边界内包含的子字符串。然而，通常的公式是，要提取的子串完全由数字组成。如果要提取的号码中有分隔符(如电话号码)，则不能使用。当然，您可以首先执行替换操作来移除字符串中的分隔符，这可能会更复杂。

本文只处理提取的字符串包含唯一数字子串的情况。

我们用例子来解释一下。首先看待提取的数字没有分隔符的情况。例如，单元格A1中的字符串如下：

账号1234567890:需要注意

显然，我们需要提取1234567890。

以下是我们使用的公式：

=-LOOKUP(1)，-(MID(A1，MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A11/17))、ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1))))“* * 0”)

请注意，必须在MID函数生成的值的末尾添加“**0”，以确保在任何情况下都能获得正确的结果。例如，如果单元格A1中的字符串是：

账号12-6月：需要注意

使用公式而不添加“**0”:

=-LOOKUP(1)，-(MID(A2，MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A21/17))、ROW(INDEX(A:A，1):INDEX(A:A，LEN(A2)))))

返回的结果不是12，而是43994，是2020年6月12日对应的序数。连接字符串“** 0”后，确保类似“12jun”的字符串变成“12jun * * 0”，这样Excel就不会把它们当成数字了。同样，这也适用于类似于科学记数法中数字的字符串。

当然，这样的字符串还必须具有保持任意数字不变的属性。字符串“**0”相当于“E0”，表示索引为0的科学计数方法，与10 ^ 0一致，因此可以保证以这种形式表示的任何数字都将保持不变。可以在工作表中执行以下测试来验证：

=0 (147"**0)

返回147。

=0 (147"**2)

返回14700。

这种方法比“E0”更好，例如：

=-LOOKUP(1)，-(MID(A2，MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A21/17))、ROW(INDEX(A:A，1):INDEX(A:A，LEN(A2))))“E0 ”)

结果是36689，因为提取的子串是“12-JunE0”，Excel认为是2000年6月12日的日期。而且，“e”在不同的环境下可能有不同的解释。

好吧。让我们看一个类似的例子，但是要提取的子串数字包含分隔符：

1-234-5678-90:号账户需要保留

使用上面给出的公式：

=-LOOKUP(1)，-(MID(A1，MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A11/17))、ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1))))“* * 0”)

1而不是我们想要的1-234-5678-90。

如上所述，首先删除分隔符并不是一件简单的事情：

=-LOOKUP(1，-MID(replace(A1，"-"，"")，MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A11/17))，ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1)))”* * 0”)

乍一看好像还可以，但是返回的结果是1234567890。留给我们的是如何将分离器重新插入正确的位置。当然，如果给定字符串的格式是固定的，比如电话号码。然而，即使如此，使用多个REPLACE/REPLACE函数可能会使公式更加复杂。

本文旨在寻找一种通用的解决方案，即如何通过确定字符串中的第一个和最后一个数字来提取子字符串，而不考虑分隔符的数量和个数，并且不需要执行替换操作。

在之前的一系列文章中，我们找到了一种非常适合确定字符串中第一个数字位置的方法，即MIN/FIND函数组合构造。然而，要找到一个确定字符串中最后一个数字的等价结构并不容易，而这是实现这一点的关键。

中间函数的参数字符数：

=MID(A1，MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A11/17))，[someconstruction])

假设您想避免[某些构造]被两个单独的子句减去，其中一个子句是字符串中第一个数字的位置，另一个子句是最后一个数字的位置。首先查看一些确定字符串中最后一个数字位置的公式结构，然后查看哪一个(如果有)也有助于找到第一个数字的位置，这可能会很有用。以下是实现这一目标的三种主要公式结构：

公式1:

=MATCH(1，0/ISNUMBER(0 MID(A1，ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1))，1)))

等式2:

=LOOKUP(1，0/ISNUMBER(0 MID(A1，ROW)(INDEX(a : a，1):INDEX(A:A，LEN(A1)))，1))、ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1)))

等式3:

=MAX(IF(ISNUMBER(0 MID(A1，ROW)(INDEX(a : a，1):INDEX(A:A，LEN(A1)))，1))、ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1))))

其中，公式1和公式3是数组公式。

显然，公式1是最好的，因为它不需要重复ROW子句。但是这个构造也可以用来查找字符串中的第一个数字吗？如果没有，方程式2可以吗？三级方程式怎么样？

我们先来试试减法，即从用于确定最后一个数字位置的子句中减去用于确定第一个数字位置的子句：

MATCH(1，0/ISNUMBER(0 MID(A1，ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1))，1)))-MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A11/17))

从而形成一种解决方案：

=MID(A1，MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A11/17))、1 MATCH(1，0/ISNUMBER(0 MID(A1，ROW)(INDEX(a : a，1):INDEX(A:A，LEN(A1))、1)))-MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A11/17))

还不错！但是我们能改进num_chars参数的构造吗？

一种方法是应用上面给出的等式3:

=MAX(IF(ISNUMBER(0 MID(A1，ROW)(INDEX(a : a，1):INDEX(A:A，LEN(A1)))，1))、ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1))))

稍作调整。在2010年及以后的版本中，Excel提供了AGGREGATE函数，不仅可以将很多CSE结构转化为非CSE，还具有标准CSE公式无法复制的其他优点。

使用AGGREGATE函数的公式3的等价公式是：

=AGGREGATE(14，6，ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1)))/ISNUMBER(0 MID(A1，ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1))，1))，1)

我们不仅可以用它来生成最大值或最小值，还可以生成包含这两个值的数组。因此，构建公式：

=MMULT(AGGREGATE({14，15}，6，ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1)))/ISNUMBER(0 MID(A1，ROW(INDEX(A:A，1):INDEX(A:A，LEN(A1)))，1)，{ 1；-1})

转换为：

=MMULT({25，13}，{ 1；-1})

其中13和25分别代表字符串中第一个和最后一个数字的位置。

但是，它还有一个缺陷，就是需要重复ROW结构。我们能否对此进行改进，找到不重复子句的公式构造？是的，您可以使用：

MATCH("* "，T(1/(1 MID(A1，ROW)(INDEX(a : a，1):INDEX(A:A，LEN(A1))，1)))，{1，0})

这类似于AGGREGATE的结构，它将一个数组传递给它的参数，并获得一个由两个结果组成的数组。上述公式转换为：

MATCH("* "，T(1/(1){ " A "；“c”；“c”；“o”；“u”；“n”；“t”；"";“N”；“o”；"."；"";"1; "-";"2;"3; "4;"-";" 5; "6;"7; "8;"-";" 9; "0;"";“r”；“e”；“q”；“u”；“我”；“r”；“e”；”s”；"";“a”；“t”；“t”；“e”；“n”；“t”；“我”；“o”；" n"})，{1，0})

转换为：

MATCH("* "，T(1/({#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！2;#VALUE！3;4;5;#VALUE！6;7;8;9;#VALUE！10;1;#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！})),{1,0})

转换为：

MATCH("* "，T({#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！0.5;#VALUE！0.333333333333333;0.25;0.2;#VALUE！0.166666666666667;0.142857142857143;0.125;0.111111111111111;#VALUE！0.1;1;#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！}),{1,0})

转换为：

MATCH(“*”，{#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！"";#VALUE！"";"";"";#VALUE！"";"";"";"";#VALUE！"";"";#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！#VALUE！},{1,0})

可以看出，不是错误值的就是数字值。指定参数匹配类型的值为一将为提供数组中最后一个非#VALUE！的位置；为0将提供第一个非#VALUE！的位置。这样，上面公式转换成：

{25,13}

现在，可以将此数组传递给求不同单价下的利润函数，以最终得出中间的函数的参数字符数参数的值。最终的公式为：

=MID(A1，MIN(FIND({1，2，3，4，5，6，7，8，9，0}，A11/17))、1 MMULT(MATCH(*)、T(1/(1 MID(A1，ROW)(INDEX(a : a，1):INDEX(A:A，LEN(A1)))))、{1，0})、{ 1；-1}))