Excel提取数字技巧大全

本文揭秘了Excel中从文字与数字混排的单元格里精准提取纯数字的四大高效方案：无需复杂编程的SUBSTITUTE+TEXTJOIN公式法、适合大批量处理且操作直观的Power Query“仅数字”提取、支持负数、小数及科学计数法等复杂格式的VBA正则自定义函数，以及针对HTML或标签化结构文本的FILTERXML解析技巧——无论你是新手还是资深用户，总有一种方法能帮你秒解数据清洗难题，告别手动复制粘贴的低效与错误。

如果您在Excel中面对包含文字和数字混合的单元格，需要从中精准分离出纯数字部分，则可能是由于原始数据格式不规范导致常规复制粘贴无法直接获取数值。以下是实现该目标的多种高效方法：

一、使用SUBSTITUTE与TEXTJOIN组合清除非数字字符

该方法适用于单元格中数字与字母、符号无规律混排（如“订单编号：A123B456”或“￥789.00元”），通过逐个替换所有非数字字符为空，再合并剩余字符形成连续数字串。

1、选中目标列右侧空白列的第一个单元格（例如B1）。

2、输入公式：=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

3、按Ctrl+Shift+Enter（Excel 2019及更早版本需数组确认；Microsoft 365或Excel 2021可直接回车）。

4、双击B1单元格右下角填充柄，向下批量应用公式。

二、利用Power Query清洗混合文本

该方法适用于大批量数据处理，支持可视化操作与步骤复用，能自动识别并保留连续数字片段（包括小数点和负号），且不依赖复杂公式。

1、选中含混合文本的数据区域，点击【数据】→【从表格/区域】，勾选“表包含标题”，点击确定。

2、在Power Query编辑器中，右键目标列 →【转换】→【提取】→【仅数字】。

3、若需保留小数，确认结果列数据类型为“小数”；若显示为文本型数字，点击列标题旁的“ABC”图标 →【转换为小数】。

4、点击【主页】→【关闭并上载】，结果将写入新工作表或覆盖原位置。

三、借助正则表达式（VBA自定义函数）精准匹配数字模式

该方法适用于存在多种数字格式（如带千分位逗号、科学计数法、负数、百分比）的复杂场景，可编写一次函数，反复调用提取首位数字、全部数字或最大数字等。

1、按Alt+F11打开VBA编辑器，点击【插入】→【模块】。

2、粘贴以下代码：

Function ExtractNumbers(cell As Range) As String

Dim regex As Object, matches As Object, match As Object

Set regex = CreateObject("VBScript.RegExp")

regex.Global = True: regex.Pattern = "-?\d+\.?\d*"

Set matches = regex.Execute(cell.Value)

For Each match In matches: ExtractNumbers = ExtractNumbers & match.Value & " ": Next

ExtractNumbers = Trim(ExtractNumbers)

3、返回Excel，在空白单元格输入公式：=ExtractNumbers(A1)

4、按Enter确认，函数将返回该单元格中所有匹配的数字（以空格分隔）。

四、使用FILTERXML提取HTML标签内嵌数字（适用于特定结构化文本）

该方法适用于单元格内容本身为类HTML格式（如“299.99”或“[id:888]”），利用XML路径语法定位并抓取标签包裹的数字值。

1、确保目标文本中存在明确包围结构，例如“价格：1500元”。

2、在空白单元格输入公式：=FILTERXML(""&SUBSTITUTE(A1,"")&"","//s[contains(.,'0') or contains(.,'1') or contains(.,'2') or contains(.,'3') or contains(.,'4') or contains(.,'5') or contains(.,'6') or contains(.,'7') or contains(.,'8') or contains(.,'9')][1]")

3、若需提取全部数字而非首个，将末尾[1]改为[2]、[3]等序号，或配合INDEX+SEQUENCE动态索引。

4、对结果列设置单元格格式为“数值”，消除前导零或补全小数位。

到这里，我们也就讲完了《Excel提取数字技巧大全》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于的知识点！