Excel利用正则计算单元格（单元格中有数字和字符或者符号）数值--REGEXP函数

<p><img src="data/attachment/forum/202601/09/094422id1lcciyssiysqly.webp" alt="jpg.webp" title="Excel教程" /></p>
' f5 y  L, Q$ h7 d' W. b. Z( s; D% E<h2>一、函数概述</h2>
4 t/ q0 @: J: [5 r) n) V7 M0 }<p>Excel 中的 <strong>REGEXP 相关函数</strong>（包括 <code>REGEXP.MATCH</code>、<code>REGEXP.REPLACE</code>、<code>REGEXP.EXTRACT</code> 等）是 Excel 365 及后续版本新增的正则表达式工具，用于高效处理文本匹配、提取、替换等场景，相比传统的 <code>FIND</code>、<code>SUBSTITUTE</code> 函数更灵活强大。</p>
<h2>二、核心函数语法及参数</h2>
<h3>1. <code>REGEXP.MATCH</code>：判断文本是否匹配正则表达式</h3>
<h4>语法</h4>
<pre><code class="language-excel">REGEXP.MATCH(text, pattern, [options])
</code></pre>
<h4>参数说明</h4>
<table>
; R. F0 B- m' l8 b) V" ?& O4 U<thead>
7 Q4 |: v6 K! h<tr>
<th>参数</th>
% l- L, R$ M% t- I<th>必选/可选</th>
<th>说明</th>
</tr>
</thead>
8 c$ Y" H3 b0 a8 [8 X. @<tbody>
<tr>
<td><code>text</code></td>
) V" u5 k7 T0 G9 B<td>必选</td>
$ D& q0 y: }( J<td>要检测的目标文本</td>
5 U% A- G7 s* H- M* R# R</tr>
<tr>
& C) w# U/ B- J7 R8 i* h( I<td><code>pattern</code></td>
<td>必选</td>
- \7 X- z, ^7 ^1 p<td>正则表达式匹配模式</td>
$ g% i- D$ y8 N- j</tr>
<tr>
<td><code>options</code></td>
; J8 O5 P  W7 j' X<td>可选</td>
3 Z7 A1 ]# g1 N4 k0 k9 z<td>匹配选项，数字组合：0 = 默认（区分大小写、单行模式）1 = 不区分大小写2 = 多行模式4 = 忽略空格和注释</td>
; w5 z% O$ @8 |- W8 B/ h</tr>
</tbody>
* `) O. Z* L- S% t' s</table>
<h4>返回值</h4>
+ w0 k  v0 [8 |<ul>
* I  c' N- h. P- T<li>匹配成功返回 <code>TRUE</code>，失败返回 <code>FALSE</code>。</li>
</ul>
6 C9 [$ d6 Z: {& M  Y: [<h3>2. <code>REGEXP.EXTRACT</code>：从文本中提取匹配正则的内容</h3>
<h4>语法</h4>
; b9 n! _8 r. {+ S2 k3 C) s# {! z<pre><code class="language-excel">REGEXP.EXTRACT(text, pattern, [group], [options])
</code></pre>
<h4>参数说明</h4>
<table>
<thead>
<tr>
<th>参数</th>
+ Z+ H' f8 h0 v5 W4 ~<th>必选/可选</th>
) c( i4 l2 _6 v<th>说明</th>
) g/ f  A+ N) i$ ^5 B. L</tr>
. T/ o6 ^2 _# g' z</thead>
<tbody>
: E: ~( ?) A& X- e/ N& Q<tr>
<td><code>text</code></td>
2 v/ D' p6 z+ C5 R& d! E& s<td>必选</td>
* V, \! k; o' C<td>目标文本</td>
! X, m" f. f* C0 h% D% G6 f. u</tr>
6 t+ r2 o- I' T, |<tr>
<td><code>pattern</code></td>
; i1 H& n2 b2 _<td>必选</td>
1 W( e1 `. ^- {6 U% K/ Q<td>正则表达式匹配模式</td>
</tr>
# t0 q" O. c& m) @( j<tr>
<td><code>group</code></td>
<td>可选</td>
5 z3 u$ f* T0 `4 u  i<td>提取的分组编号，默认 0（提取整个匹配内容）；分组由正则中的 <code>()</code> 定义</td>
. k6 {; d& n  J/ a, \5 {" S' t& `</tr>
* G+ d2 D, _# Z<tr>
  Y: f+ R; ?" s<td><code>options</code></td>
<td>可选</td>
<td>同 <code>REGEXP.MATCH</code> 的 <code>options</code> 参数</td>
</tr>
</tbody>
* {# N8 T6 H; E# w. t, X9 _</table>
<h4>返回值</h4>
<ul>
<li>返回匹配的文本内容，无匹配则返回空值。</li>
</ul>
/ C5 W0 L3 J& l. F% y<h3>3. <code>REGEXP.REPLACE</code>：替换文本中匹配正则的内容</h3>
% f, f' J* v3 t$ @7 K9 Q<h4>语法</h4>
1 t' C  @. b, v6 T. I3 t<pre><code class="language-excel">REGEXP.REPLACE(text, pattern, replacement, [options])
</code></pre>
<h4>参数说明</h4>
; {: D5 d& N( v1 K7 F<table>
<thead>
& W* ^0 X* Q9 a1 C5 ?) O0 ?* j<tr>
# [! u" W2 Q) v' g& S<th>参数</th>
<th>必选/可选</th>
<th>说明</th>
</tr>
0 a8 t- H1 ~! R7 f' I2 h; J6 @</thead>
/ s6 L# C8 g( O<tbody>
9 P. f  h& ^& ~& O. _7 `" U0 A" r7 g, I) B<tr>
<td><code>text</code></td>
<td>必选</td>
& g4 v) J: x' ]/ Z/ w5 z<td>目标文本</td>
</tr>
5 ~/ G0 l. R3 @<tr>
+ H& {1 q: ?9 g' o  j: i) n<td><code>pattern</code></td>
<td>必选</td>
<td>正则表达式匹配模式</td>
1 K# g* h# L) ^! _/ b</tr>
<tr>
<td><code>replacement</code></td>
( ~+ o* J: u6 @8 c<td>必选</td>
( a- N; }# ]( E; @<td>替换后的文本，可使用 <code>$n</code> 引用正则分组（<code>$0</code> 代表整个匹配，<code>$1</code> 代表第1个分组）</td>
</tr>
6 L: F+ V: O' T) L' I7 Y; _<tr>
<td><code>options</code></td>
) v! t1 a/ v; b2 i* D' H<td>可选</td>
<td>同 <code>REGEXP.MATCH</code> 的 <code>options</code> 参数</td>
$ V& [" p6 V# V</tr>
</tbody>
</table>
<h4>返回值</h4>
<ul>
2 ?! _2 [% v5 W9 j, I; L! \<li>返回替换后的文本内容。</li>
( F! u# G( C" I! d4 h8 o</ul>
<h2>三、常用正则表达式语法补充</h2>
<table>
<thead>
& z7 ?" t' d* X, n: z1 W3 I4 B' ^<tr>
4 V7 F7 _0 R! ^! @. w4 `6 S<th>正则符号</th>
* d* l1 ]2 w! N  E<th>说明</th>
6 G* X' C- _- a: X7 q<th>示例</th>
7 u5 v5 C# p0 X5 A  }+ `3 w</tr>
</thead>
/ Q* O) v2 y7 k! l# M/ d0 y' K<tbody>
<tr>
! i) t' L1 X9 q- I" O* o1 X9 r' f- k  z<td><code>.</code></td>
% w" c  g, r: U. m% p5 l<td>匹配任意单个字符（除换行符）</td>
<td><code>a.b</code> 匹配 <code>acb</code>、<code>aab</code></td>
</tr>
<tr>
<td><code>*</code></td>
<td>匹配前一个字符 0 次或多次</td>
<td><code>a*</code> 匹配 <code>''</code>、<code>a</code>、<code>aaa</code></td>
7 J' ^8 f! \$ _+ X</tr>
<tr>
<td><code>+</code></td>
' ?+ ~5 A  U$ y1 K7 B<td>匹配前一个字符 1 次或多次</td>
<td><code>a+</code> 匹配 <code>a</code>、<code>aaa</code>，不匹配 <code>''</code></td>
</tr>
<tr>
' N8 I( y) D* h8 T" k<td><code>?</code></td>
+ Q, r( Y2 M; v; V<td>匹配前一个字符 0 次或 1 次</td>
<td><code>a?</code> 匹配 <code>''</code>、<code>a</code></td>
6 u6 h, T, W) C6 c2 {) T! f</tr>
$ U3 x0 a' h- b/ r" _, O6 w1 J3 |# g<tr>
<td><code>^</code></td>
<td>匹配文本开头</td>
<td><code>^abc</code> 匹配 <code>abc123</code>，不匹配 <code>xabc</code></td>
</tr>
<tr>
<td><code>$</code></td>
4 w! N# N  B5 f* h) [' I' w8 S<td>匹配文本结尾</td>
9 w* \5 t# s0 o& ]# `* q<td><code>abc$</code> 匹配 <code>123abc</code>，不匹配 <code>abc123</code></td>
</tr>
9 {* D' S) M- C2 _( c<tr>
: M! K# e$ f. K& E0 S% w<td><code>[]</code></td>
. r+ ]% i8 O, s' X/ l( j! I<td>匹配括号内任意一个字符</td>
4 Q2 ^* s) W5 r5 Y8 n( H4 f! M<td><code>[abc]</code> 匹配 <code>a</code>、<code>b</code>、<code>c</code></td>
: H( e% A" {! J</tr>
<tr>
: k4 a# m7 ?! C7 k! x: N8 B" {<td><code>[^]</code></td>
' f7 R4 z7 w  O  [/ q7 C<td>匹配不在括号内的任意字符</td>
<td><code>[^abc]</code> 匹配 <code>d</code>、<code>1</code>、<code>x</code></td>
. W  U* [; G0 r: W</tr>
# D7 I5 x& H  n$ u. a<tr>
<td><code>()</code></td>
2 r. }: Q: `( h% g<td>定义分组，用于提取或引用</td>
- v9 F7 v, R7 K& u, ~3 L<td><code>(abc)123</code> 中 <code>abc</code> 为第1个分组</td>
3 o& [) r* k3 F" Z8 j! a</tr>
2 s( D+ v/ c) k" @$ V; B<tr>
' J+ K% D7 d% ^$ k/ ]<td><code>\d</code></td>
0 e* v7 B- G# h. G8 M# H<td>匹配任意数字（等价于 <code>[0-9]</code>）</td>
<td><code>\d+</code> 匹配 <code>123</code>、<code>45</code></td>
</tr>
6 s7 G4 B0 \, O* g* u<tr>
6 P) H  G" [, X# S' \<td><code>\D</code></td>
0 ]2 W; V: Q5 C) K( y<td>匹配任意非数字</td>
<td><code>\D+</code> 匹配 <code>abc</code>、<code>@#</code></td>
</tr>
  T) h( C1 F0 w% X; {<tr>
5 h1 d; p9 }0 K! V2 H4 H<td><code>\w</code></td>
<td>匹配字母、数字、下划线（等价于 <code>[a-zA-Z0-9_]</code>）</td>
7 }* t" F2 i- l3 [<td><code>\w+</code> 匹配 <code>user123</code>、<code>name</code></td>
</tr>
<tr>
/ a* `- P: v. y; K' \<td><code>\W</code></td>
<td>匹配非字母、数字、下划线</td>
; [9 s) Z! t/ l$ `<td><code>\W+</code> 匹配 <code>@</code>、<code>#$%</code></td>
* |3 ^, U/ c, f4 T$ [$ _" L</tr>
( k) p- h4 B* j- A) a  N2 Q9 {<tr>
: x7 z+ F& w- X<td><code>\s</code></td>
<td>匹配任意空白字符（空格、制表符等）</td>
) B8 M. @2 q$ p8 D. d<td><code>\s</code> 匹配空格、<code>\t</code></td>
</tr>
</tbody>
</table>
<h2>四、实战案例</h2>
<h3>案例 1：使用 <code>REGEXP.MATCH</code> 验证手机号格式</h3>
<p><strong>需求</strong>：判断 A1 单元格的文本是否为 11 位手机号（以 1 开头，第二位为 3-9）。</p>
& t: @4 L  ~: W5 Z" a( i<ul>
<li>正则模式：<code>^1[3-9]\d{9}$</code></li>
<li>公式：
- P: b& Y5 S* q1 ^* N% w<pre><code class="language-excel">=REGEXP.MATCH(A1, &quot;^1[3-9]\d{9}$&quot;)
</code></pre>
5 M4 d! w3 o* |7 x! D3 b2 ]</li>
$ s* j# P$ ~6 F& O<li>测试结果：<table>
3 J0 T" R* O: K8 v8 [<thead>
$ H: W* b6 `$ U<tr>
<th>A1 内容</th>
& [4 I+ H0 r/ E2 O# N7 X! W( i<th>公式返回值</th>
# E2 X, @0 H1 z% K& U2 h9 y7 X5 X</tr>
</thead>
<tbody>
6 y4 m# Q$ d. `9 g6 t# H) c2 K( s<tr>
6 i- j! U* g7 \<td>13800138000</td>
  W% `# o5 J" }8 A<td>TRUE</td>
7 j+ C8 V) u& R1 |9 u& o9 g</tr>
' L# s0 C- x  o; j% `: k% I  y8 L6 T) s<tr>
<td>12345678901</td>
3 ]' W* \6 u7 F8 N/ x! W<td>FALSE</td>
/ p5 ^9 f$ x% @/ a' z" o& ^</tr>
1 ?7 H' h0 t, s" @0 L  H0 z+ h1 v, V<tr>
/ o9 W8 Z5 f3 H6 p7 P<td>1380013800</td>
<td>FALSE</td>
/ h* ~* U! W  @; K7 k</tr>
</tbody>
</table>
</li>
2 f; M. r' x) N6 U" `</ul>
- A' y* x3 F, h<h3>案例 2：使用 <code>REGEXP.EXTRACT</code> 提取文本中的数字</h3>
* ~$ S$ H; H) v9 Q) i" z- B<p><strong>需求</strong>：从 A2 单元格的混合文本中提取所有数字。</p>
<ul>
7 I0 K$ f5 j& T9 S; }<li>正则模式：<code>\d+</code>（匹配1个及以上数字）</li>
<li>公式：
<pre><code class="language-excel">=REGEXP.EXTRACT(A2, &quot;\d+&quot;)
# V8 l! v8 l8 m, m$ j$ o</code></pre>
</li>
; Y2 [5 I8 ]) [0 o! |5 _<li>测试结果：<table>
9 f8 |1 V+ o7 P1 a<thead>
4 _8 s% [5 r$ y; N  |" e<tr>
<th>A2 内容</th>
<th>公式返回值</th>
+ o! E* }& m) {& S</tr>
</thead>
0 \7 j, ?5 z. ~<tbody>
<tr>
<td>订单号：20250109</td>
1 h1 d2 i2 k6 h" R- z2 y<td>20250109</td>
  _) e6 s3 m0 N/ R* n' x7 F. |( j</tr>
1 s2 U8 W0 d/ _! t<tr>
5 F( l$ }8 D5 l( e, g<td>价格：99.9 元</td>
) }3 ]% t& M+ g( ~, l$ S<td>99</td>
</tr>
<tr>
3 m# i* n9 s3 D6 v* v9 J+ U( k, J  M% x<td>无数字文本</td>
, K& a/ k' @9 c) s<td>空值</td>
</tr>
5 B" ^1 A; Q6 M8 A3 _4 F</tbody>
</table>
  u% z/ I& c2 @6 a  Q. x6 Y</li>
</ul>
2 d7 g3 C: T1 ~4 K# a) [5 I<p><strong>进阶：提取多个分组内容</strong><br />
8 G5 {4 u8 N0 U, e4 X8 H, `- E若 A3 内容为 <code>姓名：张三，年龄：25</code>，需提取姓名和年龄：</p>
& o9 p7 E  @/ h8 e- v4 O# o<ul>
<li>正则模式：<code>姓名：(\w+)，年龄：(\d+)</code></li>
  h' f; {8 D6 i- J4 ~<li>提取姓名公式：
2 A/ A2 l" @% Q% u& M<pre><code class="language-excel">=REGEXP.EXTRACT(A3, &quot;姓名：(\w+)，年龄：(\d+)&quot;, 1)
</code></pre>
</li>
<li>提取年龄公式：
: J' T2 e9 J, {* i3 h6 ^+ L<pre><code class="language-excel">=REGEXP.EXTRACT(A3, &quot;姓名：(\w+)，年龄：(\d+)&quot;, 2)
& L2 t, U3 y9 Z5 C% N; r. f* j: y</code></pre>
0 a( s5 d# c0 t, \</li>
<li>结果：姓名返回 <code>张三</code>，年龄返回 <code>25</code>。</li>
( \, Y  f! @8 C. H. o  V</ul>
4 s% M* A! s" i6 U" b<h3>案例 3：使用 <code>REGEXP.REPLACE</code> 替换文本中的敏感信息</h3>
<p><strong>需求</strong>：将 A4 单元格的手机号中间 4 位替换为 <code>****</code>。</p>
<ul>
<li>正则模式：<code>(1[3-9]\d{2})\d{4}(\d{4})</code>（分组1：前3位，分组2：后4位）</li>
( o5 t  n5 Z, C, }5 I<li>公式：
9 p8 P; V# }8 R  e* ~% u<pre><code class="language-excel">=REGEXP.REPLACE(A4, &quot;(1[3-9]\d{2})\d{4}(\d{4})&quot;, &quot;$1****$2&quot;)
</code></pre>
</li>
0 e: p" B( |* `7 G<li>测试结果：<table>
6 @  F; M2 k  b<thead>
<tr>
<th>A4 内容</th>
1 P1 D* F& u4 t3 i2 A<th>公式返回值</th>
9 G8 @/ F4 {3 ]2 p! S</tr>
$ Z5 \8 E: e/ B& r</thead>
- N" g- O. Z' u& P' ?+ m+ g<tbody>
4 d" Z6 f8 c' O& d; X<tr>
<td>13800138000</td>
$ Y) G; y1 h. ]& @0 z<td>138****8000</td>
7 ^4 z/ j  A# O) c, i- p</tr>
</tbody>
</table>
' ?  B5 g4 [% F7 Q</li>
</ul>
% R, D# P. h: `! F0 u! \  e( N<h3>案例 4：忽略大小写匹配（使用 <code>options</code> 参数）</h3>
0 R: u& q& B& l, ]% F<p><strong>需求</strong>：判断 A5 单元格是否包含 <code>excel</code>（不区分大小写）。</p>
# ?9 i, e; e( u# Z/ l" D+ u<ul>
# \5 ~" i$ A' F. b% ~  L+ l5 r<li>公式：
<pre><code class="language-excel">=REGEXP.MATCH(A5, &quot;excel&quot;, 1)
</code></pre>
% ?8 d" [( _- }3 Q</li>
<li>测试结果：<table>
<thead>
! u& ^5 \2 S2 u3 B. B5 d' {4 [<tr>
4 S# @  T# A, W<th>A5 内容</th>
<th>公式返回值</th>
</tr>
& B, O; l  i1 x4 z</thead>
7 n% R$ \1 N8 c+ o<tbody>
<tr>
' S4 u3 O2 P/ e/ @; |) H: s<td>Excel 365</td>
<td>TRUE</td>
1 n+ {+ h0 S- i8 E, k</tr>
2 `2 q' r1 I8 T# A2 m0 z8 y5 v<tr>
  y3 G4 R/ L, B+ K0 l8 a- M<td>EXCEL 2021</td>
<td>TRUE</td>
2 D$ p$ i+ @5 z</tr>
* k5 \$ z1 L" V3 y3 S<tr>
<td>word</td>
<td>FALSE</td>
- n" g5 j& P; _2 G/ n! z</tr>
4 u' \0 J5 p8 ~5 ]" a</tbody>
</table>
</li>
+ `1 g& \3 i8 P- s</ul>
# W0 Y5 Y- g8 j6 G<h2>五、注意事项</h2>
<ol>
<li><strong>版本限制</strong>：REGEXP 系列函数仅支持 <strong>Excel 365/2021 及更高版本</strong>，低版本 Excel 需借助 VBA 或第三方插件实现正则功能。</li>
<li><strong>转义字符</strong>：在 Excel 正则中，反斜杠 <code>\</code> 无需额外转义（与其他编程语言不同），例如匹配小数点直接写 <code>\.</code> 即可。</li>
1 l  w" z1 b: `<li><strong>多行模式</strong>：当 <code>options=2</code> 时，<code>^</code> 和 <code>$</code> 会匹配每行的开头和结尾，适用于处理换行符分隔的文本。</li>
5 K3 w+ Q% T& r$ C. }. g1 N4 s<li><strong>性能优化</strong>：复杂正则表达式在处理大量数据时可能较慢，建议先筛选目标数据再使用函数。</li>
" W8 E6 l6 q; p$ }</ol>
3 O) Y9 R% y! C3 `8 k