如何在Microsoft Excel中使用正则表达式(Regex),包括单元格内和循环。

regex excel vba


如何在Excel中使用正则表达式,并利用Excel强大的类似网格的设置进行数据操作?

  • In-cell函数返回一个匹配的模式或字符串中的替换值。
  • 子通过数据列循环,提取匹配到相邻单元格。
  • 需要什么设置?
  • Excel的正则表达式有哪些特殊字符?

我知道Regex在许多情况下(使用或不使用正则表达式?)并不理想,因为excel可以使用 LeftMidRightInstr 类型的命令进行类似的操作。




Answer 1 Portland Runner


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

要在Excel中使用,请遵循以下步骤。

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

  • 选择“开发人员”标签(我没有此标签,我该怎么办?
  • 在 "代码 "功能区中选择 "Visual Basic "图标。
  • 在 "Microsoft Visual Basic for Applications "窗口中,从顶部菜单中选择 "工具"。
  • 选择 "参考资料"
  • 勾选 "Microsoft VBScript Regular Expressions 5.5 "旁边的方框,将其包含在工作簿中。
  • 点击 "确定"

第2步:定义模式

基本定义:

- 范围。

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

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

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

() 将不同的匹配分组以便返回。请参阅下面的示例。

{} 乘数,用于重复定义在其之前的模式。

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

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

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

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

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

* 匹配零个或多个之前定义的模式。-例如通配符,表示可能存在或可能不存在的模式。-例如 [a-z]* 匹配空字符串或小写字母字符串。

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

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

| OR运算符

  • 例如 a|b 表示 ab 均可匹配。
  • 例如, red|white|orange 正好匹配一种颜色。

^ NOT运算符

  • 例如 [^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个字符是否为数字。如果是这样,它们将被删除并显示字符串的其余部分。如果没有,则会出现一个框,告诉您找不到匹配项。单元格 A1 的值 12abc 将返回 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”。

results image


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

本示例循环遍历一个范围( A1A2A3 ),并查找一个字符串,该字符串以三个数字开头,后跟一个字母字符,然后是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

Results:

results image


附加图案示例

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



Answer 2 Patrick Böker


为了在Excel公式中直接使用正则表达式,下面的UDF(用户定义函数)可以提供帮助。它或多或少直接将正则表达式的功能作为一个excel函数直接暴露出来。

它是如何工作的

它需要2-3个参数。

  1. 一个文本要用正则表达式上。
  2. 一个有规律的表达方式。
  3. 一个格式字符串,指定结果的外观。它可以包含 $0$1$2 等。 $0 是整个匹配项, $1 和up对应于正则表达式中的各个匹配项组。默认为 $0

一些例子

提取一个电子邮件地址。

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

结果: [email protected]

提取几个子字符串。

=regex("Peter Gordon: [email protected], 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')中,按 ALT+F11 打开Microsoft Visual Basic for Applications编辑器。
  2. 将VBA引用添加到正则表达式库(从Portland Runners ++ answer中无耻地复制):
    1. 单击工具 -> 参考(请原谅德语截图)Tools -> References
    2. 在列表中找到Microsoft VBScript正则表达式5.5,然后选中它旁边的复选框。
    3. 单击确定
  3. 单击插入模块。如果你给你的模块不同的名称确保模块并不会具有相同的名称作为下面的UDF(如命名模块 Regex 和函数 regex 导致#NAME!错误)。

    Second icon in the icon row -> Module

  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编辑器”窗口。




Answer 3 SAm


急于扩展patszim的答案

  1. 打开Excel工作簿。
  2. Alt+F11打开VBAMacros窗口。
  3. 正则表达式来下的添加引用工具,然后引用
    ![Excel VBA Form add references
  4. 并选择Microsoft VBScript正则表达式5.5
    ![Excel VBA add regex reference
  5. 插入一个新的模块(代码需要驻留在模块中,否则无法工作)。
    ![Excel VBA insert code module
  6. 在新插入的模块中。
    ![Excel VBA insert code into module
  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. ![Excel regex function in-cell usage将正则表达式模式放在其中一个单元格中,并在其上使用绝对引用。功能将与其在其中创建的工作簿联系在一起。
    如果需要在不同的工作簿中使用它,请将函数存储在Personal.XLSB中




Answer 4 Vikas Gautam


这是我的尝试。

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



Answer 5 DeezCashews


我需要将其用作单元函数(如 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. 然后,您可以在带有 =REGPLACE(B1, "(\w) (\d+)", "$1$2") (例如:“ A 243”到“ A243”)的单元格中使用