在Microsoft Excel中的正则表达式公式

众所周知，正则表达式在开发者世界中无处不在，它们用于验证网站输入的各种内容，以及在文本中搜索大字符串中的不同类型的单词模式，还有许多其他用途。

在Microsoft Excel中，正则表达式（VBA RegEx或简称VBA Regex）并不广为人知，也不太流行，因为大多数用户擅长使用简单的LEFT、RIGHT、MID和FIND函数来操作字符串。

在本教程中，我们将讨论Microsoft Excel中使用的不同概念，包括：

1. 你如何理解Microsoft Excel中的正则表达式？
2. Microsoft Excel中是否存在正则表达式函数？
3. Microsoft Excel中的正则表达式小抄是什么？
4. 你如何理解Microsoft Excel中的自定义正则表达式函数？
5. Microsoft Excel中的正则表达式匹配函数是什么意思？
6. Microsoft Excel中的正则表达式提取函数是什么意思？
7. Microsoft Excel中的正则表达式替换函数是什么意思？

你如何理解Microsoft Excel中的正则表达式

在Microsoft Excel中，正则表达式（也称为regex或regexp）是特殊编码的字符序列，主要用于定义搜索模式。通过使用该模式，我们可以有效地在给定的字符串中查找匹配的字符组合，或者验证数据输入。

正则表达式主要具有自己的语法，包括独特的字符、操作符和结构。

• 例如：[0-5]表示匹配从0到5的任何一个数字。

正则表达式主要用于各种编程语言，包括Visual Basic for Applications（VBA）和JavaScript。后者具有一个唯一的RegExp对象，可以根据需要创建自定义函数。

Microsoft Excel中是否存在正则表达式函数？

上述问题的答案是：遗憾的是，Microsoft Excel中没有内置的正则表达式函数。如果某人想要在其公式中使用正则表达式，他们需要创建自定义函数（基于VBA或.NET），或者安装通常支持正则表达式的第三方工具。

Microsoft Excel中的正则表达式小抄是什么

我们都知道，无论给定的正则表达式模式是非常简单还是非常复杂，都是由常见的语法构建而成。我们提供了对主要正则表达式模式的快速参考，这将帮助个人掌握基本知识。在学习更多示例时，它也可以作为我们的速查表。

如果我们对正则表达式感到舒适，我们还可以直接跳转到RegExp函数：

字符

Microsoft Excel使用这些最常用的模式来匹配特定的字符。

字符类

借助这些模式，我们可以轻松地分别匹配Excel表格中不同字符集的各个元素：

量词

在Microsoft Excel中，量词被称为特殊表达式，用于指定要匹配的字符总数；分别应用于其之前的字符。

分组

锚点

在Microsoft Excel中，锚点指定了一个在输入字符串中我们需要查找匹配项的位置。

Alternation（OR）构造

在Microsoft Excel中，alternation操作符启用了OR逻辑，因此我们可以有效地匹配相应的这个或者那个元素：

看向构造

在Excel中，看向构造在我们想要匹配某些东西之前或之后是否跟随着其他东西时非常有帮助。而这些表达式有时被称为“零宽断言”，因为它们有效地匹配的是位置而不是实际字符。

注意：在Visual Basic for Application (VBA)的正则表达式中，不支持后向断言。

Microsoft Excel中的自定义正则表达式函数是什么意思

从上面我们已经知道Microsoft Excel没有内置的正则表达式函数，因此为了启用正则表达式，我们都需要创建三个自定义VBA（Visual Basic for Applications）函数（也称为用户定义函数）。

VBA RegExp函数在Excel中如何工作？

现在在这个部分，我们将讨论内部机制并准确地解释后台发生了什么。

因此，当我们开始在VBA（Visual Basic for Applications）中使用正则表达式时，我们需要激活RegEx对象引用库，或者我们可以高效地使用CreateObject函数。

RegExp对象封装了四个属性，如下所示：

1. Pattern：用于匹配给定输入字符串中定义模式的特定类型。
2. Global：用于控制是否在给定输入字符串中查找所有匹配项，还是只查找第一个。
3. Multiline：可用于确定是否在多行字符串中跨越换行匹配给定模式，还是仅在第一行中进行匹配。
4. IgnoreCase：定义给定正则表达式是区分大小写（默认）还是不区分大小写。

   #示例：Microsoft Excel VBA正则表达式

在这个示例中，我们将使用RegEx.Test函数来检查给定文本中是否存在指定的模式。

我们将按照以下步骤使用VBA RegEx：

步骤1：我们需要 定义一个新的子过程以创建一个宏。

代码：

Sub RegEx_Ex1()
End Sub

步骤2： 之后，我们将使用RegEx作为可以用来创建RegEx对象的对象来定义两个变量，同时将Str定义为一个有效的字符串。

代码：

Sub RegEx_Ex1()
Dim RegEx As Object, Str As String
End Sub

步骤3：在这一步中，我们将使用CreateObject函数创建我们的RegEx对象。

代码：

Sub RegEx_Ex1 ()
Dim RegEx As Object, Str As String
Set RegEx = CreateObject ("VBScript.RegExp")
End Sub

步骤4：现在，我们将使用RegEx函数添加要测试的模式。 使用代码：

Sub RegEx_Ex1 ()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject ("VBScript.RegExp")
With RegEx
    .Pattern = "[0-9]+"
End With

End Sub

步骤 5： 在此之后，我们将有效地定义需要检查给定模式的字符串。

代码：

Sub RegEx_Ex1()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject ("VBScript.RegExp")
With RegEx
    .Pattern = "[0-9]+"
End With
Str = "My Bike Number is MH-12 PP-6145"

End Sub

步骤6：然后，我们将使用RegEx进行测试，测试给定的模式是否存在于名为str的变量中，并进行调试。

代码：

Sub RegEx_Ex1 ()

Dim RegEx as Object, Str As String
Set RegEx = CreateObject ("VBScript.RegExp")
With RegEx
    .Pattern = "[0-9]+"
End With
STR = "My Bike Number is MH-12 PP-6145"
Debug.Print RegEx. Test (Str)

End Sub

第7步: 然后，我们将使用键盘上的F5或运行按钮运行代码，以便获得输出结果。

Microsoft Excel中的正则表达式匹配函数是什么意思

众所周知，RegExpMatch函数用于搜索输入字符串中与给定正则表达式匹配的文本，如果找到匹配项，则返回“TRUE”，否则返回“FALSE”。

语法：

RegExpMatch(text, pattern, [match_case])

在其中;

• Text (必需): 包含一个或多个要搜索的字符串。
• Pattern (必需): 它是要匹配的正则表达式。
• Match_case (可选): 它主要控制是否匹配或忽略文本大小写，值为TRUE或FALSE。

# 示例: 如何使用正则表达式来匹配给定的字符串

在给定的数据集中，我们假设我们想要识别包含SKU代码的相应条目。

发现每个SKU都以2个大写字母开头，后跟一个连字符和4个数字，所以我们可以通过以下表达式来匹配它们。

Pattern: \b[A-Z]{2}-\d{4}\b

其中：

• [A-Z]{2} 表示任意2个大写字母，范围从A到Z；
• \d{4} 表示任意4位数字，范围从0到9。
• 而单词边界 \b 表示SKU是一个独立的单词，而不是一个更大的字符串的一部分。

现在根据上述模式，我们将开始像平常一样输入公式，相应函数的名称将在Excel的自动完成列表中有效显示出来：

假设原始字符串存在A5中，公式如下：

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

为了方便起见，我们可以将给定的正则表达式输入到一个单独的单元格中，并利用绝对引用（A2）来作为模式参数，以确保在将公式复制到其他单元格时，给定的单元格地址保持不变：

=RegExpMatch (A5, A2)

同时，在IF函数中使用RegExpMatch代替TRUE和FALSE来显示文本标签，并在value_if_true和value_if_false参数中指定所需的文本：

=IF(RegExpMatch(A5, A2), "Yes", "No")

什么是Microsoft Excel中的Regex Extract函数

Microsoft Excel中的RegExpExtract函数搜索与正则表达式匹配的子字符串，然后提取所有匹配或特定匹配。

公式：

RegExpExtract (text, pattern, [instance_num], [match_case])

在其中，

• Text： 需要搜索的文本字符串。
• Pattern： 被视为要匹配的正则表达式。
• Instance_num： Instance_num是可选的，主要是一个序号，表示需要提取的实例。
• Match_case： 它主要控制是否匹配或忽略文本的大小写，可以为TRUE或FALSE。

# 示例：如何通过正则表达式提取字符串

在这个示例中，我们将讨论如何提取发票号码，为此我们将使用简单的正则表达式函数来匹配任何7位数：

Pattern: \b\d{7}\b

现在，我们将把模式放在A2单元格中，并使用紧凑且简单的公式完成工作：

=RegExpExtract (A5, A2)

如果出现匹配的情况，那么一种公式将提取发票号码；而如果没有找到匹配项，则不返回任何内容：

Regex Replace函数在Microsoft Excel中的含义是什么

在Microsoft Excel中，RegExpReplace函数主要用于将与正则表达式匹配的值替换为我们指定的文本。

RegExpReplace (text, pattern, replacement, [instance_num], [match_case])

其中：

• Text： 这是需要在Excel表格中搜索的文本字符串。
• Pattern： Pattern是必填字段，是要匹配的正则表达式。
• Replacement： Replacement是用于替换匹配的子字符串的文本。
• Instance_num： 要替换的实例。默认值为“all matches”。
• Match_case： 它主要控制是否匹配或忽略文本大小写，即TRUE或FALSE。

# 示例：如何通过正则表达式替换或删除给定的字符串

我们都知道，我们的一些记录通常包含信用卡号码，这些信息非常机密。我们可以用其他内容替换它或完全删除它，这两个任务都可以通过 RegExpReplace 函数来实现。

如何实现？在第二种情况下，我们将用空字符串替换它。

在我们的示例表中，所有的信用卡号都是由16个数字组成的，分为4组，并用空格分隔，所以为了找到它们，我们将使用正则表达式的模式进行重复匹配：

Pattern: \b\d{4} \d{4} \d{4} \d{4}\b

并且可以有效地使用以下字符串进行替换：

Replacement: XXXX XXXX XXXX XXXX

这里有一个完整的公式，我们可以用它来利用敏感信息 替换 信用卡号码。

=RegExpReplace (A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "XXXX XXXX XXXX XXXX")

此外，使用在单独的单元格A2和B2中的正则表达式和替换文本，这个公式同样有效：

在Microsoft Excel中，“removing（移除）”是“replacing（替换）”的一个特例，为了 移除 信用卡号码，我们可以使用一个空字符串（””）作为替换参数：

=RegExpReplace (A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "")