Excel Regex：使用正则表达式匹配字符串

在本教程中，我们将深入了解如何使用Regex匹配Excel中的字符串。

当您需要在一系列单元格中找到某个值时，您将使用匹配或XMATCH函数。在单元格中寻找特定的字符串时，查找和搜索功能派上用场。您怎么知道单元格是否包含与给定模式相匹配的信息？显然，使用正则表达式。但是开箱即用的Excel不支持Regexes！不用担心，我们会强迫它:)

• Excel Regex匹配功能
• 比赛字符串
• 匹配号码
• 匹配电话号码
• 正则不匹配角色
• 正则不匹配字符串
• 案例不敏感的匹配
• 匹配有效的电子邮件地址
• excel如果匹配正则
• Excel Countif Regex
• 正则使用Regex工具匹配

Excel VBA Regex功能匹配字符串

从标题来看，为了在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 'array to store the results Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long 'index of the current row in the source range, index of the current column in the source range, count of rows, count of columns 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至cntinputrows，1至cntinputcols） for iinputCurrow = 1 to cntinputrows for iinputCurcol = 1 to cntinputcols to cntinputcols artres（iinputCurrow，iinputCurrow，iinputCurcol） iinputCurcol）.Value） NEXT REGEXPMATCH = arres exit函数errhandl：regexpMatch = cverr（xlerrvalue）结束功能

将代码粘贴到VBA编辑器中，您的新regexpMatch功能已准备就绪。如果您对VBA的经验不太经验，则本指南可能会有所帮助：如何在Excel中插入VBA代码。

笔记。插入代码后，请记住将文件保存为宏启用了宏观的工作簿（.xlsm）。

REGEXPMATCH语法

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

我们的自定义功能有3个参数 - 需要前两个，最后一个是可选的：

regexpMatch（文本，模式，[match_case]）

在哪里：

• 文本（必需） - 可以搜索的一个或多个字符串。可以作为单元格或范围参考提供。
• 模式（必需） - 匹配的正则表达式。直接放置在公式中时，必须以双引号封闭图案。
• match_case （可选） - 定义匹配类型。如果true或省略（默认），则执行对案例敏感的匹配；如果是错误的 - 不敏感的。

该功能在Excel 365，Excel 2021，Excel 2019，Excel 2016，Excel 2013和Excel 2010的所有版本中都起作用。

关于RegexPMatch的3件事

在进行实际计算之前，请注意以下几点阐明一些技术：

1. 该函数可以处理单个单元格或范围。在后一种情况下，如本示例所示，结果以动态阵列或溢出范围的形式返回在相邻单元格中。
2. 默认情况下，该函数对病例敏感。要忽略文本案例，请将match_case参数设置为false。由于VBA REGEXP的限制，不支持病例不敏感的模式（？i）。
3. 如果找不到有效的模式，则该函数将返回false；如果图案无效，则为#Value！发生错误。

下面，您会发现一些为了演示目的而创建的正则匹配示例。我们不能保证我们的模式将与您的真实工作表中的更多输入数据无法使用。在投入生产之前，请确保根据您的需求进行测试并调整我们的样品模式。

如何使用Regex匹配Excel中的字符串

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

假设您有一系列单元格（A5：A9），其中包含有关某些项目的各种细节。您想知道哪些细胞具有SKUS。假设每个SKU由2个大写字母，一个连字符和3位数字组成，则可以使用以下表达式匹配它们。

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

其中[az] {2}表示从a到z和\ d {3}的任何2个大写字母表示从0到9的任何3个数字。\ b字符表示一个单词边界，这意味着sku是一个单独的单词，而不是较大的字符串的一部分，例如23-MAR-2022。

建立了模式，我们可以继续编写公式。从本质上讲，使用自定义功能与本地函数没有什么不同。一旦您开始键入公式，该函数的名称将出现在Excel的自动完成建议的列表中。但是，动态阵列Excel（Microsoft 365和Excel 2021）和传统Excel（2019及以上版本）中有几个细微差别。

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

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

=RegExpMatch(A5, "\b[AZ]{2}-\d{3}\b")

该模式也可以保存在预定义的单元格中，该单元格被绝对参考锁定（$ a $ 2）：

=RegExpMatch(A5, $A$2)

在第一个单元格中输入公式后，您可以将其拖到所有其他行中。

此方法在所有Excel版本中都可以很好地工作。

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

要将多个字符串与单个公式匹配，请在第一个参数中包括一个范围参考：

=RegExpMatch(A5:A9, "\b[AZ]{2}-\d{3}\b")

在支持动态阵列的Excel 365和Excel 2021中，它以这种方式起作用 - 您在第一个单元格中键入公式，命中Enter ，公式自动溢出到下面的单元格中。

在Excel 2019及更早的时候，它仅作为传统的CSE阵列公式，该公式在一系列单元格中输入，并通过按CTRL Shift一起完成键一起完成​​。

正则匹配号码

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

匹配任何数字的正则

要匹配任意数量的任何长度，请在 /D字符之后立即放置量词，该量子说要寻找包含1个或更多位数的数字。

模式：\ d

=RegExpMatch(A5:A9, "\d ")

以匹配特定长度的等级

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

例如，要匹配由7位数字组成的发票号，您将使用\ d {7}。但是，请记住，它将与字符串中任何地方的7位数字匹配，包括10位数或100位数字。如果这不是您要寻找的，请将单词\ b放在两边。

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

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

匹配电话号码的正则

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

在下面的数据集中，我们将搜索10位数字，这些数字在前两个组中具有3位数字，最后组中有4位数字。这些组可以用一个时期，连字符或空间分开。第一组可能包含在括号中，也可能不会封闭。

模式：（\（\ d {3} \）| \ d {3}）[ - \。\ s]？\ d {3} [ - \。\。\ s]？\ d {4} \ b

分解这种正则表达式，这是我们得到的：

• 第一部分（\（\ d {3} \）| \ d {3}）匹配括号内或无括号内的3位数字。
• [ - \。\ s]？部分表示0或1在方括号中出现任何字符：连字符，周期或空格。
• 接下来，还有另外一组3位数字d {3}，然后是任何连字符，周期或whitespace [\ - \。\ s]？出现0或1次。
• 4位数字\ d {4}的最后一组之后是一个单词界\ b，以表明电话号码不能成为更大数字的一部分。

使用A5中的原始字符串和A2中的正则表达式，该公式采用此形式：

=RegExpMatch(A5, $A$2)

…并完全按照预期的方式工作：

笔记：

• 未检查国际法规，因此它们可能存在也可能不存在。
• 在正则表达式中，\ s代表任何空格字符，例如空间，标签，马车返回或新线路。要仅允许空间，请使用[ - \。 ]而不是[ - \。\ s]。

正则不匹配角色

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

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

在电话号码列表中，假设您想找到那些没有国家代码的人。请记住，任何国际代码都包含符号，您可以使用[^\]字符类来查找不包含加号的字符串。重要的是要意识到上面的表达式与任何不符合的单个字符匹配。由于电话号码可以在字符串中的任何位置，不一定在一开始，因此添加 *量词以检查每个后续字符。启动 ^和end $锚固确保整个字符串已经处理。结果，我们得到以下正则表达式，上面说“不匹配字符串中的任何位置”。

模式： ^[ ^\]*$

=RegExpMatch(A5, "^[^\ ]*$")



正则不匹配字符串

尽管没有针对特定字符串匹配的特殊正则表达语法，但您可以使用负LookAhead效仿此行为。

假设您希望找到不包含“柠檬”一词的字符串。这种正则表达方式将有效：

模式： ^（（？柠檬）。）*$

显然，这里需要一些解释。负面的lookahead（？！柠檬）向右看，看看前方是否没有“柠檬”。如果“柠檬”不存在，则点匹配除换行线以外的任何字符。上面的表达式仅执行一项检查， *量词从锚定的字符串的开头到由$锚定的字符串末端重复零或更多次。

为了忽略文本案例，我们将第三个参数设置为false以使我们的功能不敏感：

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



提示和注释：

• 上述正则只适用于单线字符串。如果是MILTI-Line字符串， ^和$字符与每行的起点和结尾匹配，而不是输入字符串的开始和结尾，因此，Regex仅在第一行中搜索。
• 要匹配不以某些文本开头的字符串，请使用正则表达式，例如 ^（？lemons）。*$
• 要匹配不以某些文本结尾的字符串，请在搜索模式中包含端字符串锚固：（？（？lemons $）。）*$

案例不敏感的匹配

在经典的正则表达式中，有一个特殊的模式，用于不敏感的匹配（？i），这在VBA Regexp中不支持。为了克服此限制，我们的自定义功能接受了名为match_case的3^个可选参数。要进行案例不敏感的匹配，只需将其设置为false即可。

假设您希望确定诸如1-MAR-22或01-MAR-2022之类的日期。为了匹配DD-MMM-YYY和D-MMM-YY格式，我们使用以下正则表达式。

模式：\ b \ d {1,2} - （Jan | Feb | Mar | Apr | Apr | May | Jun | Jun | Jun | Jun | aug | sep | sep | oct | noc | dec） - （\ d {4} | \ d {2}）\ b

我们的表达方式搜索了一组1或2位数字，其次是连字符，然后是本月缩写的缩写。这可以启用或逻辑，然后是一组4或2位数字。

为什么不使用更简单的模式，例如\ d {1,2} - [a-za-z] {3} - \ d {2,4} \ b？为了防止误报匹配，例如01-ABC-2020。

在A2中输入模式，您将获得以下公式：

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



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

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

模式：\ b [\ w \。

为了更好地了解这里发生了什么，让我们仔细研究每个部分：

• 用户名可能包括字母，数字，下划线，点和连字符。请记住，\ w与任何字母，数字或下划线匹配，我们得到以下等级：[\ w \。\ - ]
• 域名可能包括大写字母和小写字母，数字，连字符（但不在第一个或最后一个位置）和点（在子域中）。由于不允许下划线，因此我们使用的是3个不同的字符集：[A-ZA-Z0-9] [A-ZA-Z0-9 \。\ - \ - ]*[A-ZA-Z0-9]
• 顶级域由一个点组成，然后是大写和小写字母。它可以包含2到24个字母（目前最长的TLD）：\。[A-ZA-Z] {2,24}

笔记。该模式假定域名包含2个或更多字母数字字符。

使用A5中的原始文本和A5中的图案，该公式采用这种形状：

=RegExpMatch(A5, $A$2)



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

模式：\ b [\ w \。\ - ] @[A-Z0-9] [A-Z0-9 \。\ - ]*[A-Z0-9] \。

但是使您的公式不敏感：

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

如果公式与匹配正则表达式

由于内置和自定义功能的效果很好，因此没有什么可以阻止您以单个公式将它们一起使用。

要返回或计算一些正则表达式匹配的内容，并且如果不匹配其他内容，请在if的逻辑文本中嵌入自定义regexpMatch函数：

if（regexpMatch（…），[value_if_true]，[value_if_false]）

例如，如果A5中的字符串包含有效的电子邮件地址，则可以返回“是”；否则“否”。

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



计算是否匹配正则

由于本机Excel函数不支持正则表达式，因此不可能将正则态度直接放在Countis或Countifs功能中。幸运的是，您可以使用我们的自定义功能模拟此功能。

假设您已经使用了正则匹配电话号码并在B列中输出结果。要找出包含电话号码的单元格，您只需要计算B5：B9中的真实值即可。并且可以使用标准Countif公式轻松完成：

=COUNTIF(B5:B9, TRUE)



您的工作表中不需要任何额外的专栏吗？没问题。请记住，我们的自定义功能可以一次处理多个单元格，而Excel的总和可以在数组中添加值，这就是您要做的：

• 向RegexPmatch提供范围参考，因此它返回了真实和错误的值。
• 使用双重否定（ - ）将逻辑值胁到一个和零值。
• 在结果数组中获取总和函数添加1和0。

=SUM(--RegExpMatch(A5:A9, $A$2))



将终极套件的正则匹配

我们的Ultimate Suite的用户可以利用四个功能强大的正则函数，而无需在其工作簿中添加任何VBA代码，因为它们在加载项安装过程中将其顺利集成到Excel中。我们的自定义功能由标准的.NET REGEX引擎处理，并支持功能全面的经典正则表达式。

如何使用自定义RegexMatch功能

假设您已安装了最新版本的Ultimate Suite（2021.4或更高版本），则可以通过两个简单的步骤创建正则匹配公式：

1. 在“ ablebits数据”选项卡上，在文本组中，单击Regex工具。

   

2. 在Regex工具窗格上，请执行以下操作：
   • 选择源字符串。
   • 输入您的图案。
   • 选择匹配选项。
   • 要将结果作为公式，而不是值，请选择插入作为公式复选框。
   • 单击匹配按钮。

   

片刻之后，将ablebitsregexmatch函数插入数据右侧的新列中。

在下面的屏幕截图中，该函数检查列中的字符串是否包含7位数字。

尖端：

• 该函数可以通过标准插入功能对话框直接插入单元格，该函数在ablebitsudfs下进行分类。
• 默认情况下，将正则表达式添加到公式中，但您也可以将其保存在单独的单元格中。为此，只需使用单元格引用即可进行2 ^nd参数。
• 默认情况下，该函数对病例敏感。对于不敏感的匹配，请使用（？i）模式。

有关更多信息，请参阅Ablebitsregexmatch函数。

这就是如何在Excel中进行正则表达式。我感谢您阅读，并期待下周在我们的博客上与您见面！

可用下载

Excel Regex匹配示例（.XLSM文件）Ultimate Suite 14天完全功能版本（.EXE文件）

以上是Excel Regex：使用正则表达式匹配字符串的详细内容。更多信息请关注PHP中文网其他相关文章！