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

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

要在Excel中使用，请执行以下步骤：

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

• 选择“开发者”选项卡（我没有这个标签，我该怎么办？）
• 从“代码”功能区部分选择“Visual Basic”图标
• 在“Microsoft Visual Basic for Application”窗口中，从顶部菜单中选择“工具”。
• 选择“参考”
• 选中“Microsoft VBScript正则表达式5.5”旁边的框以包含在您的工作簿中。
• 点击“OK”

步骤2：定义您的模式

基本定义：

-范围。

• 例如。a-z匹配从a到z的小写字母
• 例如。0-5匹配从0到5的任何数字

[]精确匹配这些括号内的对象之一。

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

()为返回目的对不同的匹配进行分组。参见下面的示例。

{}乘法器，用于重复之前定义的模式副本。

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

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

• 例如。a+将匹配连续的a的a、aa、aaa等

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

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

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

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

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

• 例如。a.匹配以a开头并以\n以外的任何字符结尾的两个字符串

| OR运算符

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

^非运算符

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

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

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

锚定模式：

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

• 例如^a第一个字符必须是小写字母a
• 例如。^[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：以宏身份运行

以下示例宏查看单元格A1中的值，以查看前1个或2个字符是否为数字。如果是，则将删除它们并显示字符串的其余部分。如果不是，则会出现一个框，告诉您找不到匹配项。12abc的单元格A1值将返回abc，1abc的值将返回abc，abc123的值将返回“不匹配”，因为数字不在字符串的开头。

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”）放在单元格A1中。在单元格B1中输入此公式=simpleCellRegex(A1)，结果将是“abc”。

例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：拆分不同的模式

此示例循环遍历一个范围（A1、A2和A3）并查找以三位数字开头、后跟单个字母字符然后是4位数字的字符串。输出使用()将模式匹配拆分为相邻的单元格。$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. 指定结果外观的格式字符串。它可以包含$0、$1、$2等。$0是整个匹配，$1及以上对应于正则表达式中相应的匹配组。默认为$0。

一些例子

提取电子邮件地址：

=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")

搜索结果：some@email.com

提取几个子字符串：

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

搜索结果：E-Mail: some@email.com, Name: Peter Gordon

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

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

结果在：Peter Gordonsome@email.com…

如何使用

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

1. 在Excel中的启用宏的文件（'. xlsm'）中推送ALT+F11以打开适用于应用程序的Microsoft Visual Basic编辑器。
2. 将VBA引用添加到正则表达式库（无耻地复制自波特兰跑步者++答案）：
   1. 点击工具->参考文献（请原谅德语截图）
   2. 在列表中找到Microsoft VBScript正则表达式5.5并勾选它旁边的复选框。
   3. 点击好的。

3. 点击插入模块。如果你给你的模块一个不同的名字，确保模块没有与下面的UDF同名（例如，命名模块Regex和函数regex会导致#名字！错误）。

   

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. Save and close the Microsoft Visual Basic for Applications Editor window.

以下是我的尝试：

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

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

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. Then you can use in cell with =REGPLACE(B1, "(\w) (\d+)", "$1$2") (ex: "A 243" to "A243")

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

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

7. The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in.
   If there's a need for it to be used in different workbooks, store the function in 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.RegExp行导致User Defined Type Not Defined错误，但我找到了一个对我有效的解决方案。

更新RE评论w/@chrisneilsen：

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

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

您要做的是在单元格A1中放置一个示例字符串，然后测试您的strPattern。一旦工作正常，然后根据需要调整rng。

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

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

他们提供

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

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

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

替换：用不同的文本字符串替换文本字符串的一部分。

你可以像这样把它们直接输入到一个单元格中，然后就会产生你想要的任何东西

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

它们还可以很好地与其他函数组合使用，例如如果语句，如下所示：

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

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

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

虽然我们可以在是支持的内容上找到一些来源，但了解没有支持哪些元字符等将很有帮助。可以在这里中找到更深入的解释。在这个来源中提到：

“虽然“VBScript的正则表达式……版本5.5实现了许多在以前版本的VBScript中缺失的基本正则表达式特性……JavaScript和VBScript实现了Perl风格的正则表达式。然而，它们缺乏Perl和其他现代正则表达式风格中可用的许多高级特性：”

所以，没有支持的是：

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

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

P. S.你已经提到了常规的VBA方法和函数，我可以确认它们（至少对我自己来说）在RegEx失败的情况下以自己的方式提供了帮助。