excel从字符串中提取数字 ——在字符串的末尾

在前一篇文章中，我学习了在字符串开头提取数字的公式技术。在本文中，我研究了从字符串开头提取数字的技术：

1.这些数字是连续的。

2.这些连续的数字位于字符串的末尾。

3.期望的结果是将这些连续的数字返回到单个单元格。

与上一篇文章一样，对于下面研究的每个解决方案，我们需要在两种不同的情况下测试其合理性：

1.字符串中除了末尾没有数字，比如ABC456。

2.字符串中除结尾外都有数字，要么在开头，要么在中间，如123ABC456或ABC123DEF456。

最小和查找

公式1:

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

如果单元格a 1中的内容为“ABC456”，则上述公式1返回“456”。解决过程如下：

=0 MID(“ABC 456”)，MIN(FIND({0，1，2，3，4，5，6，7，8，9}，“ABC 456”1/17))，LEN(“ABC 456”))

a1后加1/17和A1后加字符串“0123456789”效果一样。主要是，A1必须与包含从0到9的所有数字的字符串连接，以防止MIN函数返回错误。如果A1不包含从0到9的所有数字，则FIND函数的返回值中至少有一个是错误值。因为MIN函数不会忽略误差值，所以整个公式的结果也会是误差值。首先，通过在A1中的字符串末尾添加适当的值(例如，1/17，3 45，“0123456789”)，可以保证该字符串将至少包含一个从0到9的数字(因此，FIND函数的所有10个返回值都是非错误值)。同时，通过将字符串放在A1的末尾，可以确保我们不会影响MIN函数所需的输出。

这样，上面的公式现在转换成：

=0 MID(“ABC 456”)，MIN(FIND({0，1，2，3，4，5，6，7，8，9}，“ABC 456”0.0588235294117647))，LEN(“ABC 456”))

您可能不知道，但在某些公式结构中，MIN函数能够强制返回数组，而无需按Ctrl Shift Enter。本例就是这种情况，因此FIND函数将对传递给它的所有10个值进行操作，而不仅仅是第一个值。因此，上述公式转换为：

=0 MID("ABC456 ")，MIN({7，19，13，14，4，5，6，21，11，17})，LEN("ABC456 "))

MIN函数返回字符串中数字的起始位置4，因此上面的公式转换为：

=0 MID(“ABC 456”，4，LEN(“ABC 456”))

请注意，MID函数(以及RIGHT函数和LEFT函数)具有一些有用的特性，当传递的参数num_chars的值大于要提取的实际字符串的长度时，不会出现错误。也就是说，如果此参数的值将导致返回值的全部或部分超出字符串的结尾，这些函数将只返回从指定位置到字符串结尾的那些字符。这里，参数num_chars的值被指定为LEN(A1)，因为不存在子字符串的长度大于字符串本身的情况。这样，上面的公式就转换成：

=0 MID(“ABC 456”，4，6)

获取结果：

456

让我们看看如果单元格a 1中的字符串包含其他数字，如123ABC456，公式1是否有效。此时，公式1被转换成：

=0 MID(" 123 ABAC 456 "，MIN(FIND({0，1，2，3，4，5，6，7，8，9} "，123ABC4560.0588235294117647 ")、LEN(A1))

转换为：

=0 MID(" 123 ABAC 456 "，MIN({10，1，2，3，7，8，9，24，14，20})，LEN(A1))

转换为：

=0 MID(“123 ab c456”，1，LEN(“123 ab c456”))

转换为：

=0 MID(“123 ab c456”，1，9)

由于字符串中其他数字的影响，传递给MID函数的找到的起始位置发生变化，因此结果是：

#VALUE！

向右查找

等式2:

=-LOOKUP(0，-RIGHT(A1，ROW(INternet(" 1: " LEN(A1)))))

这个公式类似于上一篇文章《Excel从字符串中提取数字——数字位于字符串开头》中的公式1，只是用了RIGHT函数代替了LEFT函数。

当然，如果单元格A1中的字符串类似于“JAN18”和“123E4”，则不会返回正确的结果。详细原因请参考上一篇文章。

右，匹配和是数字。

等式3:

=0 RIGHT(A1，MATCH(FALSE，ISNUMBER(0 MID(A1，1 LEN(A1)-ROW(INDIRECT(" 1: " LEN(A1))，1)))，0)-1)

这是一个数组公式。

单元格A1中的数据仍然为“ABC456”，公式3可以转换为：

=0 RIGHT("ABC456 "，MATCH(FALSE，ISNUMBER(0 MID("ABC456 "，1 6-ROW(间接(" 1:" 6))，1))，0)-1)

转换为：

=0右(“ABC456”)，MATCH(FALSE，is number(0 MID(“ABC 456”)，1 6-{ 1；2;3;4;5;6},1)),0)-1)

转换为(注意这里如何反转结果数组，以便可以从字符串的末尾到开头逐个获取字符):

=0右(“ABC456”)，MATCH(FALSE，is number(0 MID(“ABC 456”)，6；5;4;3;2;1},1)),0)-1)

转换为：

=0右(“ABC456”，MATCH(FALSE，is number(0 { 6；5;4;c；b；A})、0)-1)

通过适当的数学运算(将数组添加到0)强制将数字字符转换为数字：

=0右(“ABC456”，MATCH(FALSE，is number({ 6；5;4;#VALUE！#VALUE！#VALUE！}),0)-1)

ISNUMBER函数忽略传递给它的参数中的错误值：

=0右(“ABC456”，MATCH(FALSE，{ TRUE真；真；假；假；FALSE}，0)-1)

转换为：

=0右(“ABC456”，4-1)

其中4表示字符串“”中第一个非数字字符从右向左出现的位置，负1表示数字字符出现的位置：

=0右(“ABC456”，3)

结果是：

456

接下来，让我们看看字符串中除结尾之外的数字是否会影响最终结果。例如，如果单元格A1中的数据是“123ABC456”，则公式3可以转换为：

=0 RIGHT(" 123 ABAC 456 ")，MATCH(FALSE，is number(0 MID(" 123 ABAC 456 ")，1 LEN(" 123 ABAC 456 ")-ROW(间接(" 1: " LEN(" 123 ABAC 456 ")))，1))，0)-1)

转换为：

=0右(“123ABC456”，MATCH(FALSE，is number(0 {“6”)；"5";"4";“C”；“B”；“一”；"3";"2";"1"}),0)-1)

转换为：

=0右(“123ABC456”，MATCH(FALSE，is number(6；5;4;#VALUE！#VALUE！#VALUE！3;2;1}),0)-1)

转换为：

=0右(“123ABC456”，匹配(假，{真；真；真；假；假；假；真；真；真}，0)-1)

转换为：

=0右(“ABC456”，4-1)

其中4表示字符串“”中第一个非数字字符从右向左出现的位置，负1表示数字字符出现的位置：

=0右(“ABC456”，3)

结果是：

456

可以看出，由于数字是从右向左检索的，因此字符串中其他位置的数字不会影响结果。

右，匹配和ISERR。

等式4:

=0 RIGHT(A1，MATCH(1，0 ISERR(0 MID(A1，1 LEN(A1)-ROW(INDIRECT(" 1: " LEN(A1)))，1))，0)-1)

这是一个数组公式。

单元格A1中的数据为“123ABC456”，公式4可转换为：

=0右(“123ABC456”，MATCH(1，0 ISERR(0 {“6”；)5;"4; "c "；"b "；"一个“；”3;"2; "1}),0)-1)

转换为：

=0右(“123ABC456”，MATCH(1，0 { FALSE；假；假；真；真；真；假；假；FALSE}，0)-1)

转换为：

=0右(“123ABC456”，MATCH(1，{ 0；0;0;1;1;1;0;0;0},0)-1)

转换为：

=0右(“123ABC456”，4-1)

转换为：

=0右(“123ABC456”，3)

结果是：

456

和公式3一样，除了末尾的数字之外，字符串其他部分中数字的存在不会影响结果。