Excel混合文本提取数字用什么函数？

在EXCEL混杂的文字里找出其中的数字，首先可以用到的是REGEXP函数（适用于EXCEL365和WPS最新的版本），此函数以正则表达式的精确方式来匹配、抽取并且排列好所有的信息。如果要兼容老版本的EXCEL的话，可以使用LOOKUP加LEFT或者RIGHT的方法从文字中抽取出开头或者结尾的一串数字，也可以用TEXTJOIN加上MID数组公式的办法把所有的数字都找出来再粘贴在一起；而POWER QUERY中的“只取数字”的功能对于大规模的数据进行结构化的整理非常有效，VBA自定义函数则具有很高的灵活性。五种主要的方法各有各的优势，在不同的情况下都可以被选择使用：REGEXP因为简单快捷所以很受欢迎，数组公式由于通用性强而受到青睐，POWER QUERY的优点在于它的可视化的流程图设计，VBA则可以做到深入细致的定制——用户可以根据自己的版本支持情况、数据大小以及操作习惯来决定最适合自己的方法。

一、使用REGEXP函数是新版本Excel的最佳选择

本函数可以使用正则表达式来一次获取到整个文档里所有的符合条件的数字部分，在A2单元格中输入一个包含正则表达式的公式就可以得到第一个匹配的结果了，如果要取出来全部并且合并成一个字符串的话可以用TEXTJOIN配合REGEXP实现：=TEXTJOIN("",TRUE,REGEXP(A2,"[0-9]+"))。对于带有小数点或者负号的数字要用"[+-]?d+.?d*"来代替；只提取出“元”前面的部分可以用"[0-9.]+(?=元)"来表示。由于公式返回的是文本类型的数据，在做进一步运算之前最好先将其转换为数值形式再进行操作（比如乘以1）。此方法不需要数组确认也可以通过拖动填充的方式自动完成，并且只适用于Excel 365订阅版和WPS 2023以上版本软件当中，在较早版本的Excel中会出现#NAME?错误提示。

二、LOOKUP+LEFT/RIGHT组合：最传统的也是最好的方法

对于没有REGEXP功能的Excel 2010到2019版本，在提取开头和结尾的连续数字时分别使用=-LOOKUP(1,-LEFT(A2,ROW($1:$15)))-和=RIGHT(A2)来实现。其中$1:$15表示最大的检测位数，建议设置为大于等于15以防止较长的数字被截断。此公式的原理是通过负号强制转换以及LOOKUP模糊查找机制来自动找出最长的有效数字序列。但是它只能提取出开头或者结尾的一段连续数字，并且中间如果有文字隔开的话就会只取一个结果，不能同时获取两者的结果。

三、TEXTJOIN和MID数组公式：可以对任意级别的字符进行选择

使用公式=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))之后需要按下Ctrl+Shift+Enter进行数组计算，在此过程中会依次读取A2中的每一个字符，并检查该字符是否为数字，如果是的话就保留下来，如果不是的话就直接忽略掉，最后把所有的有效数字串在一起形成一个完整的数字字符串。本方法可以提取出所有的孤立数字（比如“货号X7Y8Z9”就会得到结果“789”），但是这个公式的长度很长而且不容易修改，在超过255个字符以上的长文本里可能会因为ROW(INDIRECT)的限制而无法正常工作。

四、Power Query 的“只处理数字”的功能：批量清洗数据的可视化工具

选择要进行的数据列，在“数据”标签下点击“从表格/区域”，勾选“表包含标题”，打开编辑框后右击列名并选择“转换”-“格式”-“提取”-只保留数字部分，整个过程不需要使用任何公式，可以一次性完成所有列的操作并且可以保存查询步骤以便于以后重复使用。特别适用于对含有地址、备注和编号混合排列的一万多行原始数据表进行整理，清洗之后的结果会变成文本类型的数字形式，在后面还可以统一设置为数值类型。

第五部分 VBA 自定义函数：为高频用户提供的长久之计

打开VBA编辑器，在其中插入一个模块，并把ExtractNumber函数的代码粘贴进去之后再回到Excel中就可以使用了，即输入=ExtractNumber(A2)来调用这个函数。此函数会逐个读取单元格中的每一个字符，并只选取ASCII码在48到57之间的数字进行拼接，可以处理各种不同长度和类型的混合文本数据，并且还可以很容易地被修改以提取带有小数点或者逗号的数值。第一次使用的时候需要开启宏功能，但是安装好之后整个工作表都可以随时调用了，比数组公式要快很多倍，特别适用于每天都要处理几百张报表的财务人员或者是电商从业者。

五种方式并不互相排斥，在实践中常常会结合起来用：首先用Power Query进行初步筛选，然后用REGEXP对某些字段进行微调；老文档更新之后优先采用REGEXP来提高可维护性。选择的标准始终是基于版本的基础、数据的数量级以及重复出现的频率——工具是用来服务人的，并不是用来反过来控制人的。