Excel中如何从混合文本提取数字？

在Excel里提取混合文本中的数字是完全可以做到的，并且有多种成熟的途径可以选择，包括公式法、VBA编程、Power Query工具、正则表达式以及LAMBDA函数等五种方式。对于一般的简单操作来说，TEXTJOIN加MID数组公式的组合形式比较适合，可以逐个字符地识别出所有的数字并进行拼接；如果要利用的是Excel 365或者WPS最新的版本的话，则可以用到REGEXP函数和正则表达式来实现精确提取；当数据量很大并且需要不断地清理的时候，Power Query里的“只取数字”的选项非常直观可靠，并且可以一次性的全部应用；而对于那些数据量巨大而且经常需要被整理的情况而言，通过VBA编写一个自定义函数ExtractNumber就可以获得很大的灵活性，在现有的工作中无缝集成进去；最后用LAMBDA创建出来的可重用命名公式还可以进一步简化操作过程，防止出现冗长的公式的重复编写。以上各种方法都已经经过了实际测试并且适用于各种不同的版本和应用场景之中。

一、公式法：TEXTJOIN和MID数组公式的运用

该方法可以应用于Excel 2016及以上的版本中，并且不需要打开宏功能，在输入的时候按住Ctrl、Shift和Enter键就可以完成操作了。具体的步骤如下：首先在目标单元格内（比如B1）输入以下公式：=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))；其中MID函数用来逐个取出A列中的每一个字符，通过双减号把它们变成数值形式进行比较判断是否有数字存在，如果有的话就用TRUE来选择所有的符合条件的字符再用TEXTJOIN函数把这些字符连接起来形成一个完整的字符串；当公式出现0或者#VALUE!这样的错误时就需要对原来的文本做进一步处理了，可以使用TRIM函数去掉多余的空格，也可以用SUBSTITUTE函数替换掉全角数字；最后得到的结果是文本格式的数据，如果要让它参与到运算当中去的话可以在外面套上VALUE函数。

使用正则表达式的REGEXP函数可以实现精确匹配

只有Excel 365订阅版、WPS Office 2023新版本才能使用，语句简单易懂并且有很强的容错能力，在A1单元格中输入=REGEXP(A1，“[0-9]+”)就可以得到第一个连续的数字串了；如果要获取所有的数字（比如“abc12de34”变成“1234”），就改成=TEXTJOIN(,””,REGEXP(A1，“[0-9]”，，TRUE))；如果有小数点的话，就把正则表达式改成“-[0-9]+\.[0-9]*”，然后用VALUE函数把结果转换成数值形式；另外在文本中含有负号或者科学计数法的时候需要扩大正则表达式到“-?[0-9]+\.[0-9]*”，并且先用SUBSTITUTE函数来调整符号的位置。此方法速度快而且可以实现通配符逻辑，并且不受数组公式的长度限制。

第三种方法就是使用Power Query进行批量清洗，这是比较适合大规模数据处理的一种方式

可以对整个一万级别的数据进行一键式的标准化处理。操作步骤如下：选择好源数据所在的那一列之后，在Power Query的工具栏里找到“从表格/区域”的按钮，并勾选上“表包含标题”，然后进入到Power Query编辑区，在右边的菜单中选择要转换的目标列，点击一下“转换”、“格式”、“提取”、“只取数字”的选项，如果此时没有看到上述选项的话（对于老版本的Power Query来说），那么就需要自己动手添加一个自定义列，并在其中输入以下公式：=List.Accumulate(Text.ToList([列名]), "", (state, current) => if Value.Is(Value.FromText(current), Number.Type) then state & current else state)，最后把结果设置为整型数值，完成以上所有步骤之后点击“关闭并上载”，新的表格就会被保存到工作簿里面去了，并且还可以实现后续的数据刷新和同步更新功能。

第四种方法是使用VBA或者LAMBDA来实现高频率使用的功能

VBA方案要打开VBA编辑器，插入一个模块，并把提取数字函数的代码粘贴到该模块中去，最后保存为一个含有宏的工作簿文件(.xlsm)。调用的时候只需要在单元格里输入“=ExtractNumber(A1)”就可以实现跨工作表引用和数组填充的功能了。而使用LAMBDA方法就比较安全一些：首先选择“公式”选项卡下的“名称管理器”，然后创建一个新的名字（比如叫它GetDigits），接着在引用位置处填写上=LAMBDA(cell,LET(str,cell,seq,SEQUENCE(LEN(str)),chars,MID(str,seq,1),FILTER(chars,ISNUMBER(--chars),"")&""))，点击确定之后再在相应的单元格内输入=GetDigits(A1)，就可以达到同样的效果了。两种方式都不会出现宏安全性提示的问题，而且LAMBDA不需要以特殊的格式来保存文件，所以它的兼容性更好一些。

五种方法各有千秋，公式法无门槛限制、正则法简洁明了、Power Query稳定可靠、VBA操作方便快捷、LAMBDA安全性高。根据所用软件版本、数据量大小以及使用频次来决定采用哪种方式。