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

excelperfect

标签：公式练习

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

下面图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($1:$10)),''))

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

=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社群下载本文配套示例工作簿。