关于从文本中提取数字，这些公式各显神通

标签：公式练习

很多时候，要达到目的并不会只有一种方法，正是这样，才会体现出创新性和创造力，也才更有趣。

下面图1所示是示例数据，从单元格内容中提取出红色标识的数字。

图1

注：示例数据来源于chandoo.org。

首先会想到的是，使用FIND函数或者SEARCH函数查找“(”和最后一个空字符的位置，然后使用MID函数取出数字，公式如下：

=MID(A1,FIND("(",A1)+1,SEARCH("",A1,FIND("(",A1))-FIND("(",A1))

或者，如果每个单元格中最后的内容都是“ days)”，也可以使用：

=MID(A1,FIND("(",A1)+1,FIND("days)",A1)-FIND("(",A1)-1)

或者：

=SUBSTITUTE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),"days",)

或者，稍微使用替换技巧：

=MID(A1,FIND("(",A1)+1,FIND("*",SUBSTITUTE(A1,"","*",3))-FIND("(",A1)-1)*1

或者：

=SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("(",A1)),CHAR(160)&"days)","")+0

或者，使用强大的LOOKUP函数：

=-LOOKUP(,-MID(A1,FIND("(",A1)+1,{1,2,3,4,5}))

如果不让我们使用FIND函数/SEARCH函数呢？

我们可以使用SUBSTITUTE函数替换数字两边的“(”和空格，再在取值，例如公式：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"",REPT(" ",100)),"(",REPT(" ",100)),200,100+LEN(A1)))

或者，更简洁一些：

=-(TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99*2),99))&")")

或者：

=1*(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"days)",""),"(","00000"),6))

更复杂一些的，使用数组公式：

=1*(MID(A1,MATCH(40,CODE(MID(A1,ROW(1:25),1)),0)+1,LEN(A1)-6-MATCH(40,CODE(MID(A1,ROW(1:25),1)),0)))

或者，简洁一些的数组公式：

=AVERAGE(IFERROR(-RIGHT(SUBSTITUTE(A1,"days",""),ROW(

或者，有创意的一个数组公式：

=NPV(-0.9,,IFERROR(MID(RIGHT(A1,8),1+LEN(RIGHT(A1,8))-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))

但只适用于单元格中的数字不大于2位数。

其实，还可以使用Excel的快速填充功能，如下图2所示。

图2

在单元格B7中输入数字23，然后单击功能区“开始”选项卡“编辑”组中的“填充——快速填充”，即可完成。

注：有兴趣的朋友可以到知识星球完美Excel社群下载本文配套示例工作簿。