正则表达式来了，Excel中的正则表达式匹配示例

引言：本文整理自ablebits.com，供有兴趣的朋友学习参考。

当需要在单元格区域中找到某个值时，可以使用MATCH函数。在单元格中查找特定字符串时，FIND函数和SEARCH函数非常方便。如何知道单元格中是否包含与给定模式匹配的信息？显然，可以使用正则表达式。

用于匹配字符串的Excel VBA正则表达式函数

要在Excel中使用正则表达式，需要创建自己的函数。幸运的是，Excel的VBA有一个内置的RegExp对象，可以在代码中使用它，如下所示：

Public Function RegExpMatch(input_range As Range,pattern As String, Optional match_case As Boolean = True) As Variant

'存储结果的数组

Dim arRes() As Variant

'源单元格区域中当前行索引值

'源单元格区域中当前列索引值

'行数, 列数

Dim iInputCurRow As Long

Dim iInputCurCol As Long

Dim cntInputRows As Long

Dim cntInputCols As Long

On Error GoTo ErrHandl

RegExpMatch = arRes

Set regex= CreateObject('VBScript.RegExp')

regex.pattern = pattern

regex.Global = True

regex.MultiLine = True

If True =match_case Then

regex.ignorecase = False

Else

regex.ignorecase = True

End If

cntInputRows = input_range.Rows.Count

cntInputCols = input_range.Columns.Count

ReDim arRes(1 To cntInputRows, 1 To cntInputCols)

For iInputCurRow = 1 To cntInputRows

For iInputCurCol = 1 To cntInputCols

arRes(iInputCurRow, iInputCurCol) =regex.Test(input_range.Cells(iInputCurRow, iInputCurCol).Value)

Next

Next

RegExpMatch= arRes

Exit Function

ErrHandl:

RegExpMatch = CVErr(xlErrValue)

End Function

RegExpMatch语法

RegExpMatch函数检查源字符串的任何部分是否与正则表达式匹配。结果是一个布尔值：如果至少找到一个匹配项，则为TRUE，否则为FALSE。

这个自定义函数有3个参数，前两个是必需的，最后一个是可选的：

RegExpMatch(text,pattern,[match_case])

其中：

• text（必需）：要搜索的一个或多个字符串。可以提供单元格或单元格区域引用。

• Pattern（必需）：要匹配的正则表达式。当直接放在公式中时，模式必须用双引号括起来。

• Match_case（可选）：定义匹配类型。如果为TRUE或省略（默认），则执行区分大小写的匹配；如果为FALSE，则不区分大小写。

该函数适用于Excel365、Excel2019、Excel2016、Excel2013和Excel2010。

关于RegExpMatch，你应该知道的三件事

在进行实际计算之前，注意以下几点：

1.该函数可以处理单个单元格或单元格区域。在后一种情况下，结果以动态数组或溢出区域的形式返回到相邻单元格中。

2.默认情况下，该函数区分大小写。要忽略文本大小写，将参数match_case设置为FALSE。因为VBA Regexp的限制，不支持不区分大小写的模式。

3.如果没有找到有效的模式，则函数返回FALSE；如果模式是无效的，则发生错误#VALUE！。

下面，介绍几个为演示目的而创建的正则表达式匹配示例。我们不能保证模式能够完美地处理真实工作表中更大范围的输入数据。在运用到正式工作表之前，确保根据需要测试和调整这些示例正则表达式。

如何使用正则表达式在Excel中匹配字符串

当所有要匹配的字符串都具有相同的模式时，正则表达式是理想的解决方案。

假设有一个单元格区域（A5:A9），其中包含有关某些项目的各种详细信息，想要知道哪些单元格含有SKU。假设每个SKU由2个大写字母、一个连字符和3个数字组成，可以使用以下表达式匹配它们。

模式：\b[A-Z]{2}-\d{3}\b

其中，[A-Z]{2}表示从A到Z的任意2个大写字母，\d{3}表示从0到9的任意3个数字。\b字符表示单词边界，意味着SKU是单独的单词，而不是较大字符串（如23-MAR-2022）的一部分。

建立了模式后，可以继续编写公式。实质上，使用自定义函数与内置函数没有什么不同。一旦开始键入公式，函数名就会出现在Excel的自动完成建议的列表中。然而，在动态数组Excel（Microsoft 365）和传统Excel（2019及更旧版本）中存在一些细微差别。

匹配一个单元格中的字符串

要匹配单个单元格中的字符串，在第一个参数中引用该单元格，第二个参数中包含一个正则表达式。

例如：

=RegExpMatch(A5, “\b[A-Z]{2}-\d{3}\b”)

模式也可以保存在预定义的单元格中，例如单元格A2，且该单元格使用绝对引用：

=RegExpMatch(A5,$A$2)

在第一个单元格中输入公式后，可以将其向下拖动到所有其他行，如下图1所示。

图1

此方法在所有Excel版本中都运行良好。

一次匹配多个单元格中的字符串

要使用单个公式匹配多个字符串，在第一个参数中包含单元格区域引用：

=RegExpMatch(A5:A9, “\b[A-Z]{2}-\d{3}\b”)

在支持动态数组的Excel365中，它的工作方式是：在第一个单元格中键入公式，按Enter键，公式将自动溢出到下面的单元格中。

在Excel2019及以前的版本中，要使用传统的数组公式，即输入完后要按Ctrl+Shift+Enter组合键。

图2

正则表达式匹配数字

要匹配0到9之间的任何单个数字，在正则表达式中使用\d字符。根据特定任务，添加合适的量词或创建更复杂的模式。

正则表达式匹配任何数字

要匹配任意长度的任何数字，将+量词放在\d字符的后面，表示查找包含1个或多个数字的数字。

模式：\d+

=RegExpMatch(A5:A9,”\d+”)

图3

正则表达式匹配特定长度的数字

如果目标是匹配包含特定位数的数值，将\d与适当的量词一起使用。

例如，要匹配正好由7位数字组成的发票号，可以使用\d{7}。但是，请记住，它将匹配字符串中任何位置的7位数字，包括10位或100位数字。如果这不是要查找的内容，应在两侧放置单词边界\b。

模式：\b\d{7}\b

=RegExpMatch(A5:A9,”\b\d{7}\b”)

图4

正则表达式匹配电话号码

由于电话号码可以以各种格式写入，因此匹配它们需要更复杂的正则表达式。

在下面的数据集中，我们将搜索前两组中有3位数字、最后一组中有4位数字的10位数字。这些组可以用句点、连字符或空格分隔。第一组可以用括号括起来，也可以不用括号括起来。

模式：(\(\d{3}\)|\d{3})[-\.\s]?\d{3}[-\.\s]?\d{4}\b

分解这个正则表达式，如下：

• 第一部分(\(\d{3}\)\d{3})匹配括号内或不带括号的3位数字。

• [-\.\s]?部分表示方括号中任何字符出现0或1次：连字符、句点或空格。

• 接下来，还有一组3位数字d{3}，后跟任何连字符、句点或空格[\-\.\s]?出现0或1次。

• 最后一组4位数字\d{4}后跟一个单词边界\b，以明确电话号码不能是较大号码的一部分。

原始字符串在A5中，正则表达式在A2中，公式采用以下形式：

=RegExpMatch(A5, $A$2)

图5

注意，在正则表达式中，\s表示任何空格字符，如空格、制表符、回车符或换行符。要仅允许空格，使用[-\.]而不是[-\.\s]。

正则表达式不匹配字符

若要查找不包含特定字符的字符串，可以使用与括号中以外的任何内容匹配的否定字符类[^]。例如：

• [^13]将匹配不是1或3的任何单个字符。

• [^1-3]将匹配不是1、2或3（即从1到3的任何数字）的任何单个字符。

在电话号码列表中，假设要查找没有国家代码的电话号码。记住，任何国际代码都包含+号，可以使用[^\+]字符类查找不包含加号的字符串。重要的是要认识到，这个表达式匹配不是+的任何单个字符。因为电话号码可以在字符串中的任何位置，不一定在最开始的位置，所以会添加*量词来检查后面的每个字符。开头的^和结尾的$锚定确保处理整个字符串。结果，得到下面的正则表达式，它表示“不匹配字符串中任何位置的+字符”。

模式：^[^\+]*$

=RegExpMatch(A5, “^[^\+]*$”)

图6

正则表达式不匹配字符串

虽然没有特殊的正则表达式语法用于不匹配特定字符串，但可以通过使用负前瞻（negativelookahead）来模拟此行为。

假设希望查找不包含单词“lemons”的字符串，正则表达式如下：

模式：^((?!lemons).)*$

显然，这里需要一些解释，对吗？负前瞻（?!lemons）向右查找，看前面是否没有单词“lemons”。如果没有“lemons”，则该点与除换行符以外的任何字符匹配。上面的表达式只执行一次检查，*量词重复它零次或多次，从由^定位的字符串开始到由$定位的字符串结束。

为了忽略文本大小写，我们将第3个参数设置为FALSE：

=RegExpMatch(A5,$A$2,FALSE)

图7

注意：

• 上述正则表达式仅适用于单行字符串。对于多行字符串，^和$字符匹配每行的开头和结尾，而不是输入字符串的开头和结尾，因此正则表达式只搜索第一行。

• 要匹配不以特定文本开头的字符串，使用正则表达式，如^(?!lemons).*$。

• 要匹配不以特定文本结尾的字符串，在搜索模式中包含结尾字符串锚定：^((?!lemons$).)*$。

用于不区分大小写匹配的正则表达式

在经典正则表达式中，有一种特殊的不区分大小写的匹配模式（?i），VBA RegExp不支持这种模式。为了克服这个限制，我们的自定义函数接受第三个可选参数match_case。要进行不区分大小写的匹配，只需将其设置为FALSE。

假设希望识别日期，例如22年3月1日或2022年3月1日。为了匹配dd-mmm-yyy和d-mmm-yy格式，我们使用以下正则表达式。

模式：\b\d{1,2}-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-(\d{4}|\d{2})\b

我们的正则表达式搜索一组1或2位数字，后跟连字符，后跟任何月份缩写，以|分隔，以启用or逻辑，后跟一组4或2位数字。

为什么不使用更简单的模式，例如\d{1,2}-[a-Za-z]{3}-\d{2,4}\b?，这是为了阻止01-ABC-2020等假匹配。

在A2中输入模式，并使用公式：

=RegExpMatch(A5, $A$2, FALSE)

图8

正则表达式以匹配有效的电子邮件地址

众所周知，电子邮件地址由4部分组成：用户名、@符号、域名（邮件服务器）和顶级域（如.com、.edu、.org等）。要检查电子邮件地址的有效性，需要使用正则表达式复制上述结构。

模式：\b[\w\.\-]+@[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+\.[A-Za-z]{2,24}\b

其中：

• 用户名可以包括字母、数字、下划线、点和连字符。记住\w匹配任何字母、数字或下划线，我们得到以下正则表达式：[\w\.\-]+

• 域名可能包括大小写字母、数字、连字符（但不在第一个或最后一个位置）和点（在子域的情况下）。由于不允许使用下划线，我们使用3个不同的字符集来代替\w:[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+

• 顶级域包含一个点，后跟大小写字母。它可以包含2到24个字母（目前存在的最长TLD）：\[A-Za-z]{2,24}

注意，该模式假定域名包含2个或更多字母数字字符。

原始文本在单元格A5中，模式在单元格A2，公式为：

=RegExpMatch(A5,$A$2)

图9

或者，可以使用更简单的正则表达式对电子邮件进行验证，并使用小写或大写字符集：

模式：\b[\w\.\-]+@[a-z0-9]+[a-z0-9\.\-]*[a-z0-9]+\.[a-z]{2,24}\b

但要使公式不区分大小写：

=RegExpMatch(A5,$A$2,FALSE)

带匹配正则表达式的Excel IF公式

由于内置函数和自定义函数配合得很好，因此没有什么可以阻止在单个公式中同时使用它们。

若要在正则表达式匹配时返回或计算某些内容，若不匹配则返回或计算其他内容，将自定义正则表达式匹配函数嵌入if的逻辑文本中：

IF(RegExpMatch(…), [value_if_true],[value_if_false])

例如，如果单元格A5中的字符串包含有效的电子邮件地址，则返回“Yes”，否则为“No”。

=IF(RegExpMatch(A5, $A$2,), 'Yes','No')

图10

如果正则表达式匹配，则计数

由于内置的Excel函数不支持正则表达式，因此无法将正则表达式直接放入COUNTIF或COUNTIFS函数中。幸运的是，可以使用我们的自定义函数模拟此功能。

假设使用了一个正则表达式来匹配电话号码，并在列B中输出结果。要找出有多少单元格包含电话号码，只需要计算单元格区域B5:B9中的TRUE值。使用标准的COUNTIF公式可以很容易地做到这一点：

=COUNTIF(B5:B9, TRUE)

图11

如果不想要辅助列，可以吗？没问题。记住，我们的自定义函数可以一次处理多个单元格，Excel的总和可以在一个数组中累加值，下面是你要做的：

• 为RegExpMatch提供一个单元格区域引用，以便它返回一个包含TRUE和FALSE值的数组。

• 使用双否定（-）将逻辑值强制转换为1和0。

• 使用SUM函数将结果数组中的1和0相加。

=SUM(--RegExpMatch(E5:E9, $A$2))

图12

使用Ultimate Suite进行正则表达式匹配

这是一个工具加载项，Ultimate套件可以利用四个功能强大的正则表达式函数，而无需将任何VBA代码添加到他们的工作簿中，因为在外接程序安装过程中，这些函数可以顺利集成到Excel中。这里的自定义函数由标准的.NET正则表达式引擎处理，并支持功能齐全的经典正则表达式。

可以到ablebits.com中下载免费版。

注：可以到知识星球完美Excel社群下载本文配套示例工作簿及加载项。