Excel提取数字技巧大全

在Excel中从混合文本字符串中精准提取数字是一大常见难题，本文系统梳理了五种实用解决方案：面向新版用户的SUBSTITUTE+TEXTJOIN函数组合、兼容老版本的数组公式技巧、灵活强大的VBA正则自定义函数、适合批量处理的Power Query图形化操作，以及开箱即用的第三方正则加载项；无论你使用的是Excel 365还是2010，是否熟悉编程，或追求效率与可维护性，都能找到适配自身场景的高效提取方法——告别手动筛选，一键揪出隐藏在文字背后的数字！

如果您在Excel中需要从包含文字和数字的混合字符串中提取纯数字，但Excel原生不支持正则表达式，需借助函数组合或外部工具实现。以下是多种可行的提取方法：

一、使用SUBSTITUTE嵌套+TEXTJOIN（适用于Excel 365/2021）

该方法通过逐个替换所有非数字字符为空，再合并剩余字符，从而提取连续或非连续数字。要求Excel版本支持TEXTJOIN与LET函数。

1、假设原始字符串在A1单元格，选中B1输入以下公式：
=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,SEQUENCE(LEN(A1)),1)),MID(A1,SEQUENCE(LEN(A1)),1),""))

2、按Enter确认，B1将显示A1中所有数字字符拼接后的结果（不含小数点、负号等）。

3、若需保留小数，需额外判断小数点位置并加入逻辑，但此公式默认仅提取0–9字符。

二、使用数组公式+SUMPRODUCT（兼容Excel 2010及以上）

该方法利用ASCII码值识别数字字符（48–57），通过构建布尔数组筛选并拼接，无需TEXTJOIN亦可实现基础提取。

1、在B1输入以下公式（按Ctrl+Shift+Enter作为数组公式）：
=CONCAT(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

2、若Excel提示“#NAME?”，说明CONCAT不可用，请改用PHONETIC配合COLUMN构造（见下法）。

3、该公式对含中文、字母、符号的字符串均有效，但会丢失数字原有顺序中的分隔结构（如“abc123def456”返回“123456”）。

三、使用VBA自定义正则函数（完全支持正则提取）

通过插入VBA模块创建RegExpExtract函数，可精准匹配整数、小数、负数、科学计数等模式，实现真正意义上的正则提取。

1、按Alt+F11打开VBA编辑器，插入→模块，粘贴以下代码：
Function RegExpExtract(str As String, pattern As String) As String
Dim regEx As Object: Set regEx = CreateObject("VBScript.RegExp")
With regEx: .Global = True: .Pattern = pattern: End With
If regEx.Test(str) Then RegExpExtract = regEx.Execute(str)(0).Value Else RegExpExtract = ""
End Function

2、返回Excel，在B1输入公式：
=RegExpExtract(A1,"\d+")

3、如需提取带小数点的数字，将"\d+"改为"[-+]?\d*\.?\d+"；启用宏的工作簿需保存为.xlsm格式，且首次使用需信任宏设置。

四、使用Power Query（适用于Excel 2016及以上）

Power Query提供图形化界面与M语言双重操作路径，适合批量处理且可保留原始列结构，支持正则匹配（通过“匹配正则表达式”高级选项）。

1、选中数据区域→数据选项卡→从表格/区域→勾选“表包含标题”→确定。

2、在Power Query编辑器中，选择含字符串的列→转换选项卡→格式→提取→数字。

3、若需更灵活控制，点击“高级选项”，输入正则表达式如"(\d+\.?\d*)"，注意：Power Query正则引擎为.NET风格，括号捕获组必须存在才能提取。

五、使用第三方加载项（如RegEx for Excel）

安装轻量级正则加载项后，可在工作表中直接调用类似=REGEXEXTRACT(A1,"\d+")的函数，语法接近Google Sheets，免写VBA且无需重启Excel。

1、访问可信来源下载RegEx for Excel插件（.xlam文件）。

2、Excel选项→加载项→转到→浏览→选中该.xlam文件→勾选启用。

3、在B1输入=REGEXEXTRACT(A1,"[-+]?\d*\.?\d+(?:[eE][-+]?\d+)?"），该模式可匹配整数、小数、负数及科学计数法，但插件需管理员权限安装。

本篇关于《Excel提取数字技巧大全》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于文章的相关知识，请关注golang学习网公众号！