认识正则:它到底解决什么问题
正则表达式(Regular Expression,常称 regex) 是一套用来描述「字符模式」的微型语言。 给它一段文本和一个模式,它就能回答三个问题:测(这段文本里有没有符合模式的部分?)、 提(把符合模式的部分取出来?)、换(把符合模式的部分替换掉?)。
138-0013-8000 ← 用
1[3-9]\d-\d{4}-\d{4} 提出手机号SO-260701-42 ← 用
SO-\d{6}-\d{2} 提出订单号1299.50 ← 用
\d+(\.\d+)? 提出金额再求和
典型使用场景
- 数据清洗:去掉电话里的空格/横杠、全角字符转半角、删除 HTML 标签、合并多余空白;
- 格式校验:手机号、邮箱、身份证、日期、URL、金额是否合规;
- 信息抽取:从一坨备注里把订单号、金额、时间、编号抠出来;
- 重排改写:把「张三丰 20260701 088」拆成结构化三列,或把姓名颠倒;
- 批处理:上千行客户数据,一条公式全列算完(动态数组)。
REGEXTEST=测、REGEXEXTRACT=提、REGEXREPLACE=换。
学会语法后,Excel 侧只是把它们包装成三个函数调用而已。正则语法大全(分 9 组)
下表按「字符 → 次数 → 位置 → 结构 → 断言」的顺序展开。每行都标出「模式 → 作用于哪段文本 → 会命中什么」, 行末的 试 按钮会把示例直接送进本章末尾的在线测试器。
[A-Z]+ 读作「A 到 Z 的字符,出现一次以上」;
^\d{11}$ 读作「从头到尾恰好 11 个数字」。
能把一个模式读成这句话,就学会了八成。常用模式速查(复制即用)
中文业务里最高频的一批模式。点「复制」带走,点「试」进测试器看它在真实样本上命中什么。
^ $ 表示「整串必须完全符合」,
用于校验场景;若你想做的是从长文本里查找/提取,请去掉首尾锚点(见第 8 节坑 #2)。Excel 里与正则打交道的四条路
写公式之前,先确认自己的 Excel 版本走哪条路。2024 年起 Microsoft 365 原生内置了正则函数(基于 PCRE2 引擎),这是官方推荐路线; 旧版(2016 / 2019 / 2021 永久版)没有,需要 VBA 或通配符兜底。
| 途径 | 适用版本 | 威力 | 入口函数 | 一句话点评 |
|---|---|---|---|---|
| ① 原生 REGEX 三函数 推荐 |
Excel 365(Windows / Mac;网页版陆续支持) | ★★★ PCRE2 | REGEXTEST · REGEXEXTRACT · REGEXREPLACE |
不依赖宏,公式里直接写,与 FILTER / XLOOKUP 天然联动,首选。 |
| ② 通配符 | 所有版本 | ★ 仅 3 个符号 | COUNTIF(S) / SUMIF(S) / VLOOKUP / 查找替换 / 筛选 | * ? ~ 三个字符,规则简单,适合"以 A 开头、含 B"这类粗匹配。 |
| ③ VBA RegExp | 2007–2021 及 365 桌面版(需另存 .xlsm、启用宏) |
★★★ 但引擎较老 | 自定义 UDF,如 =RX_TEST(A2,"...") |
没有 365 时的最强兜底;语法细节与原生函数有差异(见第 6 章案例 12)。 |
| ④ Power Query | Excel 365 / 2021「数据 → 获取和转换」 | ★★ 管道清洗 | 拆分列 / 替换值;M 语言本身没有内置正则 | 适合"一次性清洗建管道";要用正则需要自定义 M 函数,门槛略高。 |
=REGEXTEST(,
若弹出函数提示 = 可用原生三函数;若提示"无效名称/NAME",则走 VBA(案例 12)或通配符方案。顺带一提:XLOOKUP / XMATCH 也能用正则
Excel 365 的 XLOOKUP 与 XMATCH 第 4 个参数 match_mode 里,
2 = 通配符匹配,3 = 正则匹配(0 精确 / 1 精确次位 / -1 通配符…详见官方说明)。
也就是说查找也能用正则描述「长得像什么」,后面案例 10 会用到。
※ 若你同时使用 Google Sheets:它内置 REGEXMATCH / REGEXEXTRACT / REGEXREPLACE,
但引擎是 RE2,与 Excel 的 PCRE2 存在语法差异,详见第 7 章差异表。
Excel 正则三函数详解
三个函数共享同一套 PCRE2 正则语法,区别只在干什么。所有函数都区分大小写(最后一个可选参数可改为不区分)。
REGEXEXTRACT 返回的永远是文本,要当数字用请套 VALUE() 或 --。
回答「有没有」:text 中只要存在任一处匹配,返回 TRUE,否则 FALSE。
- text要检测的文本或单元格引用。
- pattern正则模式。
- case0=区分大小写(默认);1=不区分。
^ 与 $。回答「取哪段」:按模式从文本中提取子串。
- text要提取的文本或单元格引用。
- pattern正则模式;用括号
( )圈出想单独拿到的捕获组。 - mode
0=第一个匹配(默认);1=全部匹配,竖排溢出成一列;2=取第一个匹配里的各捕获组,横排溢出成一行。 - case0=区分大小写(默认);1=不区分。
VALUE() 或 --(例:提取金额再求和)。回答「换成什么」:把匹配到的文本替换成 replacement。
- text源文本或单元格引用。
- pattern要替换掉的正则模式。
- new替换文本;可用
$1、$2引用捕获组。 - occ
0=全部替换(默认);正数 n=只替换第 n 次出现;负数=从末尾往回数第 n 次。 - case0=区分大小写(默认);1=不区分。
"";重排文本 = 用 $n 调换捕获组顺序。三个共同的关键细节
- 引擎 = PCRE2:Excel 官方明确三函数使用 PCRE2「风味」——支持环视、命名组、
\d \w \s等现代特性(见第 7 章对照)。 - 公式里写正则有两条书写规则(与 JS/Python 很不一样):① 反斜杠不用双写,
\d照抄即可;② 模式里若需要匹配双引号本身,在公式字符串内把"写成""。 - 可以把 pattern 放在单元格里引用:把常用正则放在固定格(甚至命名区域)如
=REGEXTEST(A2,$J$1),改一处全表生效。 - 模式是文本常量,不是公式:pattern 不会被当作公式执行,放心写。
公式联动实战:12 个可直接套用的案例
每个案例都是「一张小表 + 一条 Excel 公式 + 逐步讲解」。小表的 A 列是原始数据(与 Excel 行号一致,
公式里写 A2 即指第一行数据),B/C 列是你在 Excel 中会看到的结果——全部由本站用同一条正则实时算给你看。
跨环境语法差异对照
同一个模式在不同工具里可能表现不同——因为你面对的是不同的正则引擎。 下表是四个最常打交道的环境:
| 特性 | Excel REGEX (PCRE2) | JavaScript | Google Sheets (RE2) | VBA RegExp (VBScript) |
|---|---|---|---|---|
正向/负向前瞻 (?=) (?! ) | ✔ | ✔ | ✘ | ✔ |
正/负后行断言 (?<=) (?<!) | ✔ | ✔ | ✘ | ✘ |
| 命名捕获组 | ✔ | ✔ | ✔ (?P<n>) | ✘ |
反向引用 \1 | ✔ | ✔ | ✘ | ✔ |
简写类 \d \w \s | ✔ | ✔ | ✔ | ✔ |
非捕获组 (?:) | ✔ | ✔ | ✔ | ✔ |
懒惰量词 *? +? | ✔ | ✔ | ✔ | △ |
内联修饰符 (?i) (?s) | ✔ | 部分 | ✘ | ✘ |
| 替换中引用捕获组 | $1 | $1 | $1 | $1 |
✔ 支持 · ✘ 不支持 · △ 写法支持程度以各引擎文档为准。
用法提醒:同一模式若在 Google Sheets 里失效,先怀疑环视 / 反向引用;
VBA 里失效,先怀疑后行断言与内联修饰符。\d \w \s 在 VBA 引擎里是可用的(不必写成 [0-9]),但整体仍建议按第 4 章的版本自测决定路线。
十个最常见的坑(含 Excel 专属)
贪婪量词「多吃多占」
.* 会一路吃到行尾,把不该包的也包进来。想取「最小的一段」,用懒惰写法:.*?、.+?。
例:从 title="A" href="B" 取引号内容,".*" 会得到 "A" href="B",".*?" 才得到 "A"。
「包含就算」不等于「整串合法」
\d{11} 在 abc13800138000123xyz 里也会命中(因为它包含 11 位数字)。
做校验必须锚定:^\d{11}$ 才表示「从头到尾恰好 11 位」。REGEXTEST 默认就是「包含」语义,最容易在这栽跟头。
中文不是 \w
\w 只含英文/数字/下划线。匹配任意汉字用 [\u4e00-\u9fa5](JS 与 PCRE2 都认),
或 PCRE2/现代 JS 的 Unicode 属性 \p{Han}(JS 需加 u 旗标)。
\s 里藏着换行
\s = 空格 + Tab + 换行 \n + 回车 \r 等。
只想匹配"普通空格"时用字面空格或 [^\S\n](非换行的空白)。
Excel 公式里写「引号」要双写
公式文本用双引号包裹模式,若要匹配的双引号本身,写成两个:=REGEXREPLACE(A2,""(.*?)"","「$1」") 中模式的 " 在公式里要输入成 ""。
EXTRACT 出来是文本,数字要转换
REGEXEXTRACT(A2,"\d+") 返回的是文本"138",求和前先 VALUE() 或加 --(一元负负)。
案例 4 的金额求和就是典型。SUM 可对 --REGEXEXTRACT(...,1) 产生的数字数组直接聚合。
#SPILL! 溢出冲突
return_mode=1(取全部)的结果会向下溢出占用若干单元格;目标区被占就报 #SPILL!。
解法:清空/移开障碍格;或用 @ 只取首个;或包 TEXTJOIN / SUM 聚合掉(案例 2、4 都是聚合写法)。
默认区分大小写
Excel 三函数默认大小写敏感。要找「abc」同时命中「ABC」,把末参设为 1:
=REGEXTEST(A2,"abc",1)。
灾难性回溯 / 全列扫描拖慢计算
避免 (a+)+、(.*)* 这类「量词套量词」——在坏输入上会指数级回溯。
另外 FILTER 套 REGEXTEST 扫描整列时,建议把数据范围收敛到实际行数(如 A2:A2000),别用整列引用。
数字单元格按「存储值」而非「显示格式」匹配
单元格若显示成 ¥1,299.5 或日期 2026/7/1,三函数匹配的是它底层存储(如 1299.5、45273 这样的序列号)。
想按显示样子匹配,先 TEXT(A2,"格式") 转文本再接正则(见案例 4 的提示)。
在线正则测试器
输入模式与文本即实时高亮命中结果。语法采用 JavaScript 引擎(与 Excel 的 PCRE2 高度一致; 差异仅集中在第 7 章表格里的极少数特性,如内联修饰符)。下方样例芯片一键载入演示。
延伸阅读与 FAQ
推荐资料
- 微软官方函数页:
REGEXTEST/REGEXEXTRACT/REGEXREPLACE(搜索函数名即可到达,support.microsoft.com) - regex101.com —— 多引擎(含 PCRE2)在线调试,自带解释与替换预览;
- MDN Web Docs:RegExp —— JavaScript 引擎的权威语法参考;
- regular-expressions.info —— 老牌引擎特性对比库,查 VBA / RE2 细节用它。
FAQ
我的 Excel 是 2016/2019/2021 永久版,能用 REGEXTEST 吗?
同一段文本在测试器里命中,粘进 Excel 却不生效,为什么?
^ $、
或把末参写成 1;其次检查公式里的引号书写(坑 #5)。最后确认版本确实带原生函数(敲 =REGEXTEST( 有联想)。REGEXEXTRACT 返回一堆 #N/A / 空格 / 溢出,怎么收敛?
#N/A 形式占位(溢出数组特性)。
若要干净输出,用 TEXTJOIN("、",TRUE,...) 拼成一段,或用 FILTER(...,ISNUMBER(...)) 过滤掉错误值。网上找的「万能邮箱正则」怎么有的灵有的不灵?
\w、
中文字符、连续点号的处理也不同。务实做法:校验用「宽进」模式(别拒掉合法地址),后续再结合业务二次确认;提取用去锚定版本。