湖南龙网

  1. 首页 > 生活百科 > >

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

小知识生活百科

在很多情况下 ， 我们都面临着需要确定字符串中第一个和最后一个数字的位置的问题 ， 这可能是为了提取包围在这两个边界内的子字符串 。然而 ， 通常的公式都是针对所需提取的子字符串完全由数字组成 ， 如果要提取的数字中有分隔符（例如电话号码）则无法使用 。当然 ， 可以先执行替换操作来去掉字符串中的分隔符 ， 这可能会更复杂些 。
【Excel公式技巧：同时定位字符串中的第一个和最后一个数字】本文仅涉及被提取的字符串内包含唯一的数字子字符串的情况 。
我们以示例来讲解 。先看一下要提取的数字中没有分隔符的情形 ， 例如在单元格A1中的字符串如下：
Account No. 1234567890: requires attention
显然 ， 我们要提取出1234567890 。
下面是我们曾经使用的一个公式：
=-LOOKUP(1,-(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))
注意 ， 必须在MID函数生成的值的末尾添加“**0” ， 以保证能够在任何情况下都得到正确的结果 。例如 ， 如果单元格A1中的字符串是：
Account No. 12-Jun: requires attention
使用没有添加“**0”的公式：
=-LOOKUP(1,-(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))))
返回的结果不是12 ， 而是43994 ， 即日期2020-6-12对应的序数 。连接字符串“** 0”后 ， 确保类似于“12–Jun”的字符串变为“12–Jun**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},A2&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))&”E0″))
得到的结果是36689 ， 因为提取的子字符串为“12-JunE0” ， Excel认为是日期2000-6-12 。并且 ， “E”在不同的环境中可能有不同的解释 。
好了！下面让我们看看一个相似的例子 ， 但要提取的子字符串数字中包含有分隔符：
Account No. 1-234-5678-90: requiresattention
使用上面给出的公式：
=-LOOKUP(1,-(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))
返回1 ， 而不是我们想要的1-234-5678-90 。
正如上文提出的 ， 先删除分隔符并不是一件简单的事：
=-LOOKUP(1,-(MID(SUBSTITUTE(A1,”-“,””),MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))
乍一看似乎可以 ， 但返回的结果是1234567890 。留给我们的是 ， 如何在正确的位置重新插入分隔符？当然 ， 如果所给字符串的格式是固定的 ， 例如电话号码 。然而 ， 即便如此 ， 使用多个REPLACE/SUBSTITUTE函数可能使公式更复杂 。
本文寻找的是如何通过确定字符串中的第一个和最后一个数字来提取出子字符串的一种通用解决方案 ， 而不管分隔符是什么、有多少 ， 并且不需要执行替换操作 。

推荐阅读


上一篇：Excel公式：将不是素数的数分解为素数的乘积

下一篇：在Word中创建对Excel表的动态链接