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

当需要在单元格区域中找到某个值时，可以使用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)

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,A2)

在第一个单元格中输入公式后，可以将其向下拖动到所有其他行，如下图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。

模式：bd{7}b

=RegExpMatch(A5:A9,”bd{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, A2)

图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,A2,FALSE)

图7

注意：

上述正则表达式仅适用于单行字符串。对于多行字符串，^和$字符匹配每行的开头和结尾，而不是输入字符串的开头和结尾，因此正则表达式只搜索第一行。
要匹配不以特定文本开头的字符串，使用正则表达式，如^(?!lemons).*$。
要匹配不以特定文本结尾的字符串，在搜索模式中包含结尾字符串锚定：^((?!lemons).)*。

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

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

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

模式：bd{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, A2, 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,A2)