如何在 Microsoft Excel 中单元格内和循环中使用正则表达式 （Regex）

正则表达式用于模式匹配。

若要在 Excel 中使用，请按照下列步骤操作：

步骤 1：添加对“Microsoft VBScript 正则表达式 5.5”的 VBA 引用

• 选择“开发人员”选项卡（我没有此选项卡，我该怎么办？)
• 从“代码”功能区部分选择“Visual Basic”图标
• 在“Microsoft Visual Basic for Applications”窗口中，从顶部菜单中选择“工具”。
• 选择“参考资料”
• 选中“Microsoft VBScript 正则表达式 5.5”旁边的框以包含在工作簿中。
• 点击“确定”

第 2 步：定义模式

基本定义：

-范围。

• 例如 匹配从 A 到 Z 的小写字母a-z
• 例如 匹配 0 到 5 之间的任意数字0-5

[]与这些括号内的对象完全匹配。

• 例如 匹配字母 A[a]
• 例如 匹配单个字母，可以是 a、b 或 c[abc]
• 例如 匹配字母表中的任何单个小写字母。[a-z]

()对不同的匹配项进行分组以进行返回。请参阅以下示例。

{}之前定义的模式的重复副本的乘数。

• 例如 匹配两个连续的小写字母 A：[a]{2}aa
• 例如 匹配至少一个和最多三个小写字母 ， ，[a]{1,3}aaaaaa

+匹配之前定义的模式的至少一个或多个。

• 例如 将匹配连续的 a、、、，依此类推a+aaaaaa

?匹配零个或之前定义的模式之一。

• 例如，模式可能存在也可能不存在，但只能匹配一次。
• 例如 匹配空字符串或任何单个小写字母。[a-z]?

*匹配之前定义的零个或多个模式。

• 例如，可能存在或不存在的模式的通配符。
• 例如 匹配空字符串或小写字母字符串。[a-z]*

.匹配除换行符以外的任何字符\n

• 例如 匹配以 a 开头并以 anything 结尾的双字符字符串，但a.\n

|OR 运算符

• 例如 表示可以匹配或匹配。a|bab
• 例如 完全匹配其中一种颜色。red|white|orange

^NOT 运算符

• 例如 字符不能包含数字[^0-9]
• 例如 字符不能是小写或大写[^aA]aA

\转义后面的特殊字符（覆盖上述行为）

• 例如 ， ， ， ， ，\.\\\(\?\$\^

---

锚定模式：

^匹配必须发生在字符串的开头

• 例如 第一个字符必须是小写字母^aa
• 例如 第一个字符必须是数字。^[0-9]

$匹配必须发生在字符串的末尾

• 例如 最后一个字符必须是小写字母a$a

---

优先级表：

Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |

---

预定义的字符缩写：

abr    same as       meaning
\d     [0-9]         Any single digit
\D     [^0-9]        Any single character that's not a digit
\w     [a-zA-Z0-9_]  Any word character
\W     [^a-zA-Z0-9_] Any non-word character
\s     [ \r\t\n\f]   Any space character
\S     [^ \r\t\n\f]  Any non-space character
\n     [\n]          New line

---

示例 1：作为宏运行

以下示例宏查看单元格中的值，以查看前 1 个字符还是 2 个字符是数字。如果是这样，它们将被删除，并显示字符串的其余部分。如果没有，则会出现一个框，告诉您未找到匹配项。单元格值将返回，value of 将返回，value of 将返回“Not Matched”，因为数字不在字符串的开头。A1A112abcabc1abcabcabc123

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range
    
    Set Myrange = ActiveSheet.Range("A1")
    
    If strPattern <> "" Then
        strInput = Myrange.Value
        
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With
        
        If regEx.Test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

---

示例 2：作为单元内函数运行

此示例与示例 1 相同，但设置为作为单元内函数运行。若要使用，请将代码更改为：

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String
    
    
    strPattern = "^[0-9]{1,3}"
    
    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""
        
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With
        
        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

将字符串 （“12abc”） 放在单元格中。在单元格中输入此公式，结果将为“abc”。A1=simpleCellRegex(A1)B1

---

示例 3：环通范围

此示例与示例 1 相同，但循环遍历一系列单元格。

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range
    
    Set Myrange = ActiveSheet.Range("A1:A5")
    
    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value
            
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With
            
            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

---

示例 4：拆分不同的模式

此示例遍历一个范围 （， & ），并查找以三位数字开头的字符串，后跟一个字母字符，然后是 4 位数字。输出使用 将模式匹配拆分为相邻单元格。 表示在第一组 中匹配的第一个模式。A1A2A3()$1()

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range
    
    Set Myrange = ActiveSheet.Range("A1:A3")
    
    For Each C In Myrange
        strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"
        
        If strPattern <> "" Then
            strInput = C.Value
            
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With
            
            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

结果：

---

其他模式示例

String   Regex Pattern                  Explanation
a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceding alpha character
a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters

</i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit

要直接在 Excel 公式中使用正则表达式，以下 UDF（用户定义的函数）可能会有所帮助。它或多或少地直接将正则表达式功能公开为 excel 函数。

运作方式

它需要 2-3 个参数。

1. 要使用正则表达式的文本。
2. 正则表达式。
3. 一个格式字符串，指定结果的外观。它可以包含 、 、 等。 是整个匹配项，而 up 对应于正则表达式中相应的匹配组。缺省值为 .$0$1$2$0$1$0

一些例子

提取电子邮件地址：

=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")

结果：[email protected]

提取多个子字符串：

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

结果：E-Mail: [email protected], Name: Peter Gordon

要将单个单元格中的组合字符串拆分为多个单元格中的组件：

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

结果：...Peter Gordon[email protected]

如何使用

要使用此 UDF，请执行以下操作（大致基于此 Microsoft 页面。他们那里有一些很好的附加信息！

1. 在 Excel 中启用宏的文件 （'.xlsm'） 中，按下以打开 Microsoft Visual Basic for Applications 编辑器。ALT+F11
2. 添加对正则表达式库的 VBA 引用（无耻地复制自 Portland Runners++ 答案）：
   1. 点击工具->参考（请原谅德语截图）
   2. 在列表中找到 Microsoft VBScript 正则表达式 5.5 并勾选它旁边的复选框。
   3. 单击“确定”。

3. 单击“插入模块”。如果为模块指定不同的名称，请确保该模块的名称与下面的 UDF 不同（例如，命名模块和函数会导致 #NAME！ 错误）。Regexregex

   

4. 在中间的大文本窗口中，插入以下内容：

   Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
       Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
       Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
       Dim replaceNumber As Integer
   
       With inputRegexObj
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .Pattern = matchPattern
       End With
       With outputRegexObj
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .Pattern = "\$(\d+)"
       End With
       With outReplaceRegexObj
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
       End With
   
       Set inputMatches = inputRegexObj.Execute(strInput)
       If inputMatches.Count = 0 Then
           regex = False
       Else
           Set replaceMatches = outputRegexObj.Execute(outputPattern)
           For Each replaceMatch In replaceMatches
               replaceNumber = replaceMatch.SubMatches(0)
               outReplaceRegexObj.Pattern = "\$" & replaceNumber
   
               If replaceNumber = 0 Then
                   outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
               Else
                   If replaceNumber > inputMatches(0).SubMatches.Count Then
                       'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                       regex = CVErr(xlErrValue)
                       Exit Function
                   Else
                       outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                   End If
               End If
           Next
           regex = outputPattern
       End If
   End Function
   

5. 保存并关闭“Microsoft Visual Basic for Applications 编辑器”窗口。

这是我的尝试：

Function RegParse(ByVal pattern As String, ByVal html As String)
    Dim regex   As RegExp
    Set regex = New RegExp
    
    With regex
        .IgnoreCase = True  'ignoring cases while regex engine performs the search.
        .pattern = pattern  'declaring regex pattern.
        .Global = False     'restricting regex to find only first match.
        
        If .Test(html) Then         'Testing if the pattern matches or not
            mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
            RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
        Else
            RegParse = "#N/A"
        End If 
    End With
End Function

我需要将其用作单元格函数（如或），并发现它很容易：SUMVLOOKUP

1. 确保您位于启用宏的 Excel 文件中（另存为 xlsm）。
2. 开放式开发人员工具Alt + F11
3. 添加 Microsoft VBScript 正则表达式 5.5，如其他答案

4. 在工作簿或其自己的模块中创建以下函数：

   Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
       Dim regex As New VBScript_RegExp_55.RegExp
       Dim strInput As String
   
       strInput = myRange.Value
   
       With regex
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .Pattern = matchPattern
       End With
   
       REGPLACE = regex.Replace(strInput, outputPattern)
   
   End Function
   

5. 然后您可以在单元格中使用（例如：“A 243”到“A243”）=REGPLACE(B1, "(\w) (\d+)", "$1$2")

扩展 patszim 对那些匆忙的人的回答。

1. 打开 Excel 工作簿。
2. Alt+F11打开 VBA/宏窗口。
3. 在“工具”下添加对正则表达式的引用，然后在“引用”下添加对正则表达式的引用
   
4. 并选择 Microsoft VBScript 正则表达式 5.5
   
5. 插入一个新模块（代码需要驻留在模块中，否则它不起作用）。
   
6. 在新插入的模块中，
   

7. 添加以下代码：

   Function RegxFunc(strInput As String, regexPattern As String) As String
       Dim regEx As New RegExp
       With regEx
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .pattern = regexPattern
       End With
   
       If regEx.Test(strInput) Then
           Set matches = regEx.Execute(strInput)
           RegxFunc = matches(0).Value
       Else
           RegxFunc = "not matched"
       End If
   End Function
   

8. 正则表达式模式放置在其中一个单元格中，并对其使用绝对引用。函数将与在其中创建函数的工作簿相关联。
   如果需要在不同的工作簿中使用它，请将该函数存储在 Personal.XLSB 中

这是一个函数。例子：regex_subst()

=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo

这是简化的代码（无论如何，对我来说更简单）。我无法弄清楚如何使用上述内容构建合适的输出模式来像我的示例一样工作：

Function regex_subst( _
     strInput As String _
   , matchPattern As String _
   , Optional ByVal replacePattern As String = "" _
) As Variant
    Dim inputRegexObj As New VBScript_RegExp_55.RegExp

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With

    regex_subst = inputRegexObj.Replace(strInput, replacePattern)
End Function

我不想启用参考库，因为我需要我的脚本是可移植的。该行导致错误，但我找到了适合我的解决方案。Dim foo As New VBScript_RegExp_55.RegExpUser Defined Type Not Defined

更新 RE 评论，@chrisneilsen：

在我的印象中，启用参考库与本地计算机设置相关联，但实际上它直接与工作簿相关联。因此，您可以启用参考库，共享启用宏的工作簿，最终用户也不必启用该库。注意：后期绑定的优点是，开发人员不必担心在用户计算机上安装的对象库版本错误。这可能不是库的问题，但我并不认为“性能”的好处对我来说是值得的，因为我们在我的代码中谈论的是难以察觉的毫秒。我觉得这应该更新一下，以帮助其他人理解。如果你启用参考库，你可以使用“早期绑定”，但如果你不这样做，据我所知，代码可以正常工作，但你需要“后期绑定”并放松一些性能/调试功能。VBScript_RegExp_55.RegExp

来源：https://peltiertech.com/Excel/EarlyLateBinding.html

你要做的是在单元格中放一个示例字符串，然后测试你的.一旦工作正常，请根据需要进行调整。A1strPatternrng

Public Sub RegExSearch()
'https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
'https://wellsr.com/vba/2018/excel/vba-regex-regular-expressions-guide/
'https://www.vitoshacademy.com/vba-regex-in-excel/
    Dim regexp As Object
    'Dim regex As New VBScript_RegExp_55.regexp 'Caused "User Defined Type Not Defined" Error
    Dim rng As Range, rcell As Range
    Dim strInput As String, strPattern As String
    
    Set regexp = CreateObject("vbscript.regexp")
    Set rng = ActiveSheet.Range("A1:A1")
        
    strPattern = "([a-z]{2})([0-9]{8})"
    'Search for 2 Letters then 8 Digits Eg: XY12345678 = Matched

    With regexp
        .Global = False
        .MultiLine = False
        .ignoreCase = True
        .Pattern = strPattern
    End With

    For Each rcell In rng.Cells

        If strPattern <> "" Then
            strInput = rcell.Value

            If regexp.test(strInput) Then
                MsgBox rcell & " Matched in Cell " & rcell.Address
            Else
                MsgBox "No Matches!"
            End If
        End If
    Next
End Sub

这不是一个直接的答案，但可能会提供更有效的替代方案供您考虑。也就是说，Google 表格有几个内置的正则表达式函数，这些函数非常方便，有助于规避 Excel 中的一些技术程序。显然，在PC上使用Excel有一些优势，但对于绝大多数用户来说，Google表格将提供相同的体验，并可能在文档的可移植性和共享方面提供一些好处。

他们提供

REGEXEXTRACT：根据正则表达式提取匹配的子字符串。

REGEXREPLACE：使用正则表达式将文本字符串的一部分替换为其他文本字符串。

SUBSTITUTE：用字符串中的新文本替换现有文本。

REPLACE：将部分文本字符串替换为其他文本字符串。

您可以像这样直接在单元格中键入这些内容，并生成您想要的任何内容

=REGEXMATCH(A2, "[0-9]+")

它们还可以与其他函数（例如 IF 语句）结合使用，如下所示：

=IF(REGEXMATCH(E8,"MiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*")/1000,IF(REGEXMATCH(E8,"GiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*"),"")

希望这为那些对 Excel 的 VBS 组件感到畏惧的用户提供了一种简单的解决方法。

为了增加有价值的内容，我想创建这个提醒，说明为什么有时 VBA 中的正则表达式并不理想。并非所有表达式都受支持，但可能会抛出一个，并可能让作者猜测（我自己就是受害者）。Error 5017

虽然我们可以找到一些关于支持的内容的来源，但了解哪些元字符等不受支持会很有帮助。可以在此处找到更深入的解释。在此来源中提到：

“虽然”VBScript 的正则表达式......版本 5.5 实现了许多基本正则表达式功能，这些功能在早期版本的 VBScript 中是缺失的。...JavaScript 和 VBScript 实现了 Perl 样式的正则表达式。然而，它们缺乏 Perl 和其他现代正则表达式风格中可用的大量高级功能：”

---

因此，不支持以下情况：

• 字符串 ancor 的开头，或者使用插入符号来匹配字符串中第一个字符之前的位置\A^
• 字符串末尾 ancor ，或者使用美元符号来匹配字符串中最后一个字符之后的位置\Z$
• 正向 LookBehind，例如：（同时支持正向 LookAhead）(?<=a)b
• 负 LookBehind，例如：（同时支持负 LookAhead）(?<!a)b
• 原子分组
• 所有格量词
• Unicode，例如：\{uFFFF}
• 命名捕获组。或者，使用编号的捕获组
• 内联修饰符，例如：（区分大小写）或（全局）等。通过对象属性>（如果可用）进行设置。/i/gRegExpRegExp.Global = TrueRegExp.IgnoreCase = True
• 条件
• 正则表达式注释。在脚本中添加这些常规注释'

---

我已经不止一次在 VBA 中使用正则表达式碰壁了。通常使用，但有时我什至忘记了修饰符。我自己没有经历过上述所有这些背景，但我想我会尝试参考一些更深入的信息来扩展。随意评论/更正/添加。大声喊叫 regular-expressions.info 以获取丰富的信息。LookBehind

附言您提到了常规的 VBA 方法和函数，我可以确认它们（至少对我自己）在正则表达式失败的情况下以自己的方式有所帮助。