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

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

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

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

四、提取数值的万能公式

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

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

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

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

LOOKUP查询有三个特性：

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

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

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

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

五、提取字符的万能公式

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

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

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

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

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

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

1.使用MID和ROW组合，将每一个字符逐一提取出来。

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

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

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

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

今天的你是否已有同感？

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

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

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

做Excel高手，快速提升工作效率，部落窝教育视频和直播课全心为你！

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

提取身份证号码（上篇）《》

提取身份证号码（下篇）

合并后的单元格是（3+4），而结果单元格没有合并，要分别得到7的值。如何引用sum公式。