Excel从文字数字混排中提取数字？

Excel可以准确地从包含文字和数字混合排列的单元格中提取出数字来，并且有多种方式并且很可靠。对于微软365用户来说可以直接使用TEXTJOIN+MID数组公式或者REGEXP正则函数一次性把所有的数字都连接起来或者是按照一定的规则去匹配；而对于Excel 2007之后版本而言大多数都可以用SUMPRODUCT+MID的方法来实现；Power Query提供了只取数字的功能图方便快捷地对上千条数据进行清洗只需要三次点击就可以搞定；VBA自定义函数给使用者以字符级别的控制权可以在不同工作簿之间重复利用；快速填充（Ctrl+E）如果样本模式已经确定的话那么就不用任何公式也没有任何代码了而且效率非常高。五种途径各有千秋能够满足各种场景下的需求。

一、TEXTJOIN+MID数组公式：适合于使用的是Microsoft 365或者Excel 2019以上版本，在目标单元格内输入以下等式:=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")),此公式可以逐个检测出A1中的每一个字符，并且根据是否为数字来决定保留还是舍弃，最后把所有的符合条件的字符按照顺序连接起来形成一个完整的字符串。为了使上述等式成为数组公式，在输入完毕之后需要按下Ctrl+Shift+Enter组合键进行确认，否则会得到错误提示信息；如果想要提取出第一个连续的数字部分（比如“订单号AB123-456”只取其中的“123”，那么就需要借助到FIND和MATCH函数来进行定位起始位置，并利用MID函数截取相应的长度）。

二、REGEXP正则匹配法：只适用于Excel 365订阅版以及一些新版本的WPS软件中，在语法上比较简单明了。比如要提取出所有的数字并求和的话就可以用到下面这样的公式来实现：=SUM(REGEXP(A2,"[0-9]+"))*1；如果需要保留小数的话就稍微修改一下公式为：=SUM(REGEXP(A2,"[0-9.]+"))*1；再进一步地限制住只提取出“元”前面的部分金额的话，则可以这样写：=REGEXP(A2,"[0-9.]+(?=元)").此函数可以直接调用正则引擎而不需要嵌套太多的条件判断语句，使得整个公式的长度减少了大约六成左右，并且还能够支持前瞻性的断言之类的高级规则，在旧版本下会出现#NAME?错误的情况发生时，请先在“文件->帐户->关于Excel”的地方查看自己的Office版本号是否符合要求后再继续操作。

第三种方式是使用Power Query进行简单的数据清理，在大批量的数据处理中可以起到很好的效果，并且不需要任何专业知识就可以完成操作。首先选择要进行清洗的一列数据，然后在菜单栏里找到“数据”，再点击“从表格/区域”，勾选“表有标题”，之后就会进入到编辑界面；接着对目标列执行右键菜单下的“转换为”-“格式化”-“提取”，只保留数字部分并且保持原来的排列顺序；如果缺少上述步骤的话，则可以通过手动创建一个新的字段来实现同样的目的，在该字段内输入公式Text.Select([列名],{"0","1","2","3","4","5","6","7","8","9"})，最后点击“关闭并加载”，此时的结果会立即显示出来，在之后的数据源发生变化时只需要右击刷新即可，非常适合用来做财务报表或者电商订单这样的定期工作。

第四种是VBA自定义函数ExtractNumbers，在办公环境中经常要用到并且要长久地维护下去的情况下使用。打开编辑器（按Alt+F11），插入一个模块，在里面粘贴出标准函数代码来，利用for循环对每一个字符进行检查，用isnumeric函数判断该字符是不是一个单独的数字，并把这些单独的数字一个个地拼接起来形成完整的字符串。部署之后可以在任何一张工作表中输入=ExtractNumbers(A1)来调用这个函数，可以跨工作簿引用，并且还可以添加去重、分割符插入或者位数校验等功能，在第一次使用的时候需要把工作簿保存为可执行宏的.xlsm文件格式，并且要在信任中心开启VBA宏功能。

第五种方式是快速填充（Ctrl+E），这是最简单的一种方法，它依靠的是数据的规律性。首先在相邻的一列中手动输入第一行的数据结果（比如A1单元格中的内容为“运费¥89.5”，而B1单元格的内容则为89.5），然后选择B1到B10之间的所有单元格，并按下Ctrl+E组合键，Excel就会自动检测出其中的数据规律，并且把这一规律应用到整个这一列的所有单元格里去。此方法不需要记住任何公式，在没有宏安全提示的情况下只需要花费大约五秒钟的时间就可以对一百多行的数据进行处理了。但是这种方法的前提条件是被填充的那一列必须要有两个或者三个以上的典型例子来表现出来一种统一的形式，如果这些例子之间存在逻辑上的差异的话（例如有的带有单位符号而有的却没有），那么识别出来的准确性将会大大降低。

因此这五种方式并不是互相排斥的关系，而是一个从简单到复杂的工具链条：日常应急使用Ctrl+E，常规办公用TEXTJOIN公式，专业分析用REGEXP，工程化的运维用Power Query，深度定制用VBA来完成。