二维码 购物车
部落窝在线教育欢迎您!

Excel数字提取技巧:从包含文字的单元格中提取所有数字的万能公式

 

作者:小花来源:部落窝教育发布时间:2020-07-29 15:26:59点击:917

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!有没有能把任何文本中包含的所有数字都提取出来的公式?当然是有的,今天就给大家带来提取数字的万能公式,不管数字在文本中的位置是否有规律,不管文本中数字有多少,它都能把数字提取出来。赶紧来看看吧!

 

在上一篇文章中,小花讲解了通过观察混合文本特征,设置特定公式,完成数据提取的三种情景。于是,有些小花瓣悄悄跟小花说:小花老师,我笨,看不出数据特征,我又懒,不想分情景设置不同公式,有没有那种霸王级万能公式,啥混合文本咱都可以硬上弓?

 

答案自然是,有的!不过,还是要区分两种情况。一种是提取数值,有正负之分大小之别,也有小数点;另一种是提取数字字符串,如电话号码、身份证号码等,这里的数字没有小数和负号,也没大小之分。

 

 

这两种情景的万能公式分别该怎么写,又该怎么理解呢?且听小花细细道来。

 

 

四、提取数值的万能公式

 

 

情景特征:除了目标数值,文本中不存在其他数字,否则容易产生干扰。

 

万能公式:

{=-LOOKUP(9^9,-MIDB(A2,MIN(FINDB(LEFT(ROW($1:$11)-2,1),A2&-1/19)),ROW($1:$100)))}

 

 

公式详细拆解如下:

 

①LEFT(ROW(1:11)-2,1)

 

ROW(1:11)很好理解,返回第1行到第11行的行号,也就是11个字符组成的集合A{1,2,3…11}-2则变为字符集B{-1,0,1,2…9}。再通过LEFT提取字符集B左侧的第一个字符,生成字符集C{"-",0,1,2,…9},也就是符号和0-9这十个字符,所有数值,均由这11个字符构成。

 

综上,该部分的功能就是构建阿拉伯数字全部字符,这些数字有助于我们锁定位置,进而提取阿拉伯数值。

 

 

②FINDB(①,A2&-1/19)

 

FINDB是查找字符所在目标文本中的位置,它与FIND的差异是,它返回字节序号,即把汉字和中文符号视为2个字节。由此可知,A2单元格混合文本中,负号“-”出现的位置是5,而不是3

 

该公式中使用了A2&-1/19是为了确保字符集C{"-",0,1,2,…9}的每一个字符均在FIND的查找文本中出现,确保FIND的返回值不存在错误值。片段返回字符集C{"-",0,1,2,…9}A2&-1/19出现的位置,即序数集D{5,13,10,6,…}

 

 

③MIN(②)

 

MIN(②)的结果序数集D{5,13,10,6,…}中的最小值,它就是目标数值在A2中的起始位置,即A2混合文本中,首次出现负号或阿拉伯数字的位置,即是目标提取数值的起始位置。这就是为什么要求目标数字的左侧,不能有无关的阿拉伯数字或负号的原因。

 

 

④-MIDB(A2,③,ROW($1:$100))

 

这里使用MIDB,而不是MID,是为了对应FINDB,通过字节位置截取部分文本。ROW($1:$100)返回有序数组{1-100},作为MIDB函数的第三个参数——要提取的字节数,即分别提取1-100个字符。

 

于是,MIDB函数的功能就是从确定的起始位置开始,分别从A2单元格文本中截取长度为1-100个字节的100不等长字符串E{"-","-2","-29","-299",…"-299.19"}。而-MIDB则是将不等长字符串执行减法运算,使得非数值数据因无法运算而报错为#VALUE!,进而将不等长字符串E转化为纯数字和错误值#VALUE!组成的新常量数组F{#VALUE!;2;29;299;299;299.1;299.19;…;299.19}

 

 

⑤-LOOKUP(9^9,④

 

LOOKUP查询有三个特性:

 

1.默认查询区域是升序的,即越往后值越大。

2.返回值应小于且最接近于查询值。

3.忽略查询区域中的错误值。

 

由此,我们赋予查询值一个极大数9^9,因为LOOKUP的特性1,所以查询区域的最后一个非错误值为最大值,即该值为返回值。LOOKUP的这几个特性,完美地做到了忽略错误值取最后一个有效值!

 

图片

 

 

五、提取字符的万能公式

 

 

用法:依次提取目标单元格的全部数值并合并。

 

万能公式:

 

{=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100),ROW($1:$100))+1,1)*10^ROW($1:$100)/10)}

 

 

公式简要拆解如下:

 

① ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100)

 

通过MID(A2,ROW($1:$100),1)逐一提取每一个字符,使用双负号运算,区分数字和其它字符,再使用ISNUMBER函数判断每一个字符是否为数字,返回一组逻辑值,最后*ROW($1:$100)使得数字返回其在A2混合文本中的位置,其他字符返回0

 

② LARGE(①,ROW($1:$100))

 

通过LARGE函数,将中的字符位置值集合从大到小重新排序。由于数字在文本中的位置总是大于0,且数字越靠后,位置值越靠前。而其他字符总是小于0的。这里的重点是将所有的0值置后,同时将所有数字位置值倒排。

 

③ MID(0&A2,②+1,1)

 

MID根据的位置值+10&A2中逐一取数。由于非数字的位置值为0,所有非数字返回值均取首位0,其余数字不受影响。由于的数字位置值是颠倒的,所以,此时提取出的数字前后也是颠倒的。

 

④ SUM(③*10^ROW($1:$100)/10))

 

前三步得到了A2单元格中的所有数字和一串代表非数字位置的0组成的有序数组,此时要完成最终的提取,还需要将数字正序排列、去除0值并将其合并。这些通通交由*10^ROW($1:$100)/10完成,它通过构建一个多位数来将各个数字顺序摆放,最终将代表文本的有效数位前的0值省略,其余数字按次序从个位开始向左排列。最终的多位数即数字提取结果。

 

其实,提取数字字符串的问题,19年以后版本有了一个很简单又不烧脑的解决方案––通过CONCAT直接连接就行了。

19版万能公式如下:

 

{=CONCAT(IFERROR(--MID($A2,ROW($1:$100),1),""))}

 

 

公式简要说明:

 

1.使用MIDROW组合,将每一个字符逐一提取出来。

2.通过双负号区分数字和非数字,非数字将报错。

3.IFERROR将非数字错误值转化为空。

4.使用CONCAT函数将所有数字合并。

 

 

曾经有位朋友跟小花说

 

『文本函数才是函数界的黑洞,永远无法看透』

 

今天的你是否已有同感?

 

莫怕莫怕,洗把脸重头再看一遍。

 

『宝剑锋从磨砺出,梅花香自苦寒来!』

 

 

 

本文配套的练习课件请加入QQ群:1043683754下载。

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

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

提取数字(技巧篇)《Excel数字提取技巧:从无规律文本中提取手机号的5种方法》

提取数字(场景篇)《Excel数字提取技巧:用简单公式从混合文本中提取数字的3种情景

提取身份证号码(上篇)2019年全网最全—excel提取身份证信息合集!(建议收藏)-上篇

提取身份证号码(下篇)《2019年全网最全—excel提取身份证信息合集!(建议收藏)-下篇》