编按:从文本中提取数字是必须掌握的技能。如果每个文本只含有一个数字,提取很简单,我们前面的教程有讲过。但如果每个文本中含有多个数字,怎么提取特定或指定的某个类型的数据呢?今天就来说说。

在咱们往期的教程中,列举过很多数据不规范带来的麻烦问题。

那么到底什么才是规范的数据呢?

有一条很重要:一个单元格只列举一个信息

但是在日常工作中,总是能见到一个单元格列举一大串信息的情况,比如下面这个数据:

打开网易新闻 查看精彩图片

A列的备注看起来是不是很详细,很清晰,录入人员几乎把所有的信息都放在里面了。

但是现在要提取每一行数据中的金额(元前面的数字)就变得异常困难。

关于提取数字的公式,咱们之前有一篇公式大全专门讲解过:如何在文本中提取数字?

但这些方法都是针对单元格里只有一个数字的情况。

而今天遇到的这个问题,每个单元格里都有好几个数字,怎么办呢?

在学习Excel的过程中,遇到麻烦的问题,一时找不到思路的时候,只有一个办法,从数据源去寻找规律,只要发现规律,就能想到办法。

以下就针对这个案例来分享几种解决方案,利用的都是不同的规律,希望能够让大家有所启发。

思路1:提取“元”左边的内容,再去除多余的字符。

公式为=SUBSTITUTE(LEFT(A2,FIND("元",A2)-1),"2022年",)

打开网易新闻 查看精彩图片

思路解析:既然要提取的金额后面都有个“元”字,那就先用咱们讲过的LEFT-FIND组合先把“元”前面的内容提取出来看看是什么结果。

公式=LEFT(A2,FIND("元",A2)-1),结果如下所示。

打开网易新闻 查看精彩图片

看到这个结果是不是眼前一亮的感觉,只要再把2022年去掉就OK了。

要实现这个目标只需要再嵌套一个SUBSTITUTE函数就可以了,关于这个函数的用法,可以看看之前的教程:SUBSTITUTE函数用法

这样得到的结果是文本格式的,如果后续还需要求和的话,公式还得加工一下,将文本转为数值。

公式为=--SUBSTITUTE(LEFT(A2,FIND("元",A2)-1),"2022年",""),两个减号也可以改成1*或者0+等等,目的都是通过计算来实现格式的转换。

思路2:提取“年”与“元”之间的内容,如果没有“年”字,则间提取“元”左边的内容。

这种思路是提取两个关键字之间的内容,有个公式套路:

=MID(A2,FIND("关键字1",A2,1)+1,FIND("关键字2",A2,1)-FIND("关键字1",A2,1)-1)

本例的两个关键字分别是“年”和“元”,所以公式为:

=MID(A2,FIND("年",A2,1)+1,FIND("元",A2,1)-FIND("年",A2,1)-1)

打开网易新闻 查看精彩图片

但是由于有一些单元格里没有“年”字,结果就会出错,因此需要在公式中添加一个IFERROR函数,完整的公式为:

=MID(A2,IFERROR(FIND("年",A2,1),)+1,FIND("元",A2,1)-IFERROR(FIND("年",A2,1),)-1)

打开网易新闻 查看精彩图片

公式看起来有点长,但是逻辑不难理解。

思路2的拓展性比较强,可以延伸出一些其他的公式写法,有兴趣的同学可以自己研究一下,如果想到不一样的公式可以留言分享。

思路3:提取“元”字左边的最后一个数字

公式为=-LOOKUP(1,-RIGHT(LEFT(A2,FIND("元",A2)-1),ROW($1:$20)))

打开网易新闻 查看精彩图片

思路解析:首先利用LEFT-FIND函数组合将“元”字左边的内容截取出来,金额就变成字符串最右边的数字了。

打开网易新闻 查看精彩图片

然后再利用LOOKUP-RIGHT组合截取出最右边的数字即可,这个组合的原理参考LOOKUP函数的相关教程,这里就不赘述了。

总之,规范的数据源是比较少见的,不规范的才是常态。当遇到不规范数据源带来麻烦的时候,一定要仔细寻找规律,找到规律问题就能解决。

做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

学习交流请加微信:hclhclsc进微信学习群。

如何提取品牌信息?LOOKUP函数有绝招!

没有Textjoin函数,如何解决提取数据的问题?

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。