ARTICLE · 1178955
第11讲:Excel内置正则表达式革命——REGEXTEST与REGEXEXTRACT
第11讲:Excel内置正则表达式革命——REGEXTEST与REGEXEXTRACT
一、三大正则函数的本质差异
REGEXTEST | |||
REGEXEXTRACT | |||
REGEXREPLACE |
函数可用性：Excel 365订阅版（2023年后推送）或Microsoft 365网页版。
二、REGEXTEST：布尔判断引擎
语法结构
=REGEXTEST(text, pattern)参数拆解
实战案例：身份证号合法性验证
需求：A列包含待验证的身份证号，B列输出验证结果。
正则规则：
18位数字： ^\d{18}$17位数字+末位X： ^\d{17}[0-9Xx]$合并规则： ^(\d{18}|\d{17}[0-9Xx])$
公式：
=REGEXTEST(A2,"^(\d{18}|\d{17}[0-9Xx])$")逻辑解析：
^：字符串开头锚点\d{18}：连续18个数字|：或运算符[0-9Xx]：末位字符范围$：字符串结尾锚点
测试数据集：
三、REGEXEXTRACT：精准抽取引擎
语法结构
=REGEXEXTRACT(text, pattern, [match_instance], [case_sensitive])参数矩阵
实战案例1：从混合文本中提取手机号
原始数据（A列）：
联系张三：13812345678，备用号18898765432客户李四mobile:15987654321公式：
=REGEXEXTRACT(A2,"1[3-9]\d{9}")正则规则拆解：
1：固定首位[3-9]：第二位范围（3到9）\d{9}：后续9位数字
输出结果：
提取第二个手机号：
=REGEXEXTRACT(A2,"1[3-9]\d{9}",2)实战案例2：从发票文本中抽取金额
原始数据（A列）：
合计：￥1,234.56元 税额120.00Total: $9,876.54 USD公式（抓取所有数字+小数点格式）：
=REGEXEXTRACT(A2,"\d{1,3}(,\d{3})*(\.\d{2})?")正则规则拆解：
\d{1,3}：1到3位数字(,\d{3})*：千分位分隔符（0次或多次）(\.\d{2})?：小数点+两位小数（可选）
输出结果：
实战案例3：邮箱地址批量抽取
原始数据（A列）：
请发送至 zhangsan@company.com.cn 或 lisi@subdomain.example.org公式：
=REGEXEXTRACT(A2,"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")正则规则拆解：
[a-zA-Z0-9._%+-]+：邮箱用户名部分@：分隔符[a-zA-Z0-9.-]+：域名主体\.[a-zA-Z]{2,}：顶级域名（至少2个字母）
输出结果：
四、REGEXREPLACE：批量替换引擎
语法结构
=REGEXREPLACE(text, pattern, replacement, [case_sensitive])实战案例1：清洗文本中的所有数字
原始数据（A列）：
产品编号ABC123XYZ456，价格89.50元公式（移除所有数字）：
=REGEXREPLACE(A2,"\d+","")输出结果：
产品编号ABCXYZ，价格.元实战案例2：手机号中段脱敏
原始数据（A列）：
13812345678公式：
=REGEXREPLACE(A2,"(\d{3})\d{4}(\d{4})","$1****$2")正则规则拆解：
(\d{3})：捕获组1（前3位）\d{4}：中间4位（不捕获）(\d{4})：捕获组2（后4位）$1****$2：用捕获组拼接替换
输出结果：
138****5678实战案例3：统一日期格式
原始数据（A列）：
2025/01/152025-1-5公式（统一为YYYY-MM-DD）：
=REGEXREPLACE(A2,"(\d{4})[/-](\d{1,2})[/-](\d{1,2})","$1-"&TEXT($2,"00")&"-"&TEXT($3,"00"))简化公式（纯正则方案）：
=REGEXREPLACE(REGEXREPLACE(A2,"(\d{4})[/-](\d)[/-](\d{1,2})","$1-0$2-$3"),"(\d{4}[/-]\d{2})[/-](\d)$","$1-0$2")五、三函数组合拳：复杂文本解析
场景：从合同文本中同时提取手机号、邮箱和金额
原始数据（A列）：
甲方：张三 13812345678 zhangsan@company.com 合同金额：￥50,000.00元解决方案（B、C、D列分别提取）：
=REGEXEXTRACT(A2,"1[3-9]\d{9}") | ||
=REGEXEXTRACT(A2,"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}") | ||
=VALUE(REGEXREPLACE(REGEXEXTRACT(A2,"￥[\d,]+\.\d{2}"),",","")) |
D列公式逻辑链：
REGEXEXTRACT提取带千分位和货币符号的金额REGEXREPLACE移除千分位逗号VALUE转换为数值型
六、常用正则表达式速查表
1[3-9]\d{9} | ||
^(\d{18}|\d{17}[0-9Xx])$ | ||
[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,} | ||
\d{1,3}(,\d{3})*(\.\d{2})? | ||
\d{4}-\d{2}-\d{2} | ||
[\u4e00-\u9fa5]+ | ||
^\d+$ | ||
[a-zA-Z] |
七、执行级综合案例：客户信息清洗工作表
业务需求
从A列混合文本中提取并验证：
手机号（B列） 手机号合法性（C列） 邮箱（D列） 邮箱域名（E列）
原始数据（A列）
张三 13812345678 zhangsan@qq.com李四 1381234567X lisi@company.com.cn王五 13912345678 invalid@com公式配置表
=REGEXEXTRACT(A2,"1[3-9]\d{9}") | ||
=REGEXTEST(B2,"^1[3-9]\d{9}$") | ||
=REGEXEXTRACT(A2,"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}") | ||
=REGEXEXTRACT(D2,"@(.+)$") |
执行步骤
在B2单元格输入手机号提取公式，下拉填充至B4 在C2单元格输入验证公式，下拉填充至C4 在D2单元格输入邮箱提取公式，下拉填充至D4 在E2单元格输入域名提取公式，下拉填充至E4
最终输出表格
数据验证层
在F列添加综合验证公式：
=IF(AND(C2=TRUE,LEN(E2)>3),"有效数据","需人工复核")验证逻辑：
手机号通过正则验证 邮箱域名长度大于3（排除@com类无效格式）
八、性能对比：正则函数vs传统文本函数
MIDFIND+多层嵌套（需5-8个辅助列） | REGEXEXTRACT | ||
FIND("@")LEN+多条件IF | REGEXTEST | ||
REPLACE | REGEXREPLACE |
九、故障排查清单
#VALUE! | ||
case_sensitive参数为TRUE |
十、三个关键认知
正则函数≠替代传统函数：简单场景（如固定位置提取）用 MID效率更高测试优先于部署：在regex101.com验证规则后再写入Excel 通配符≠正则表达式：Excel的 *和?通配符与正则的.*和.规则完全不同
