为那些赶时间的人扩展patszim的答案。

Open Excel workbook. Alt+F11 to open VBA/Macros window. Add reference to regex under Tools then References and selecting Microsoft VBScript Regular Expression 5.5 Insert a new module (code needs to reside in the module otherwise it doesn't work). In the newly inserted module, add the following code: 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 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

我不想启用一个参考库，因为我需要我的脚本是可移植的。Dim foo作为新的VBScript_RegExp_55。RegExp行导致了User Defined Type Not Defined错误，但我找到了一个适合我的解决方案。

更新RE评论w/ @chrisneilsen:

I was under the impression that enabling a reference library was tied to the local computers settings, but it is in fact, tied directly to the workbook. So, you can enable a reference library, share a macro enabled workbook and the end user wouldn't have to enable the library as well. Caveat: The advantage to Late Binding is that the developer does not have to worry about the wrong version of an object library being installed on the user's computer. This likely would not be an issue w/ the VBScript_RegExp_55.RegExp library, but I'm not sold that the "performance" benifit is worth it for me at this time, as we are talking imperceptible milliseconds in my code. I felt this deserved an update to help others understand. If you enable the reference library, you can use "early bind", but if you don't, as far as I can tell, the code will work fine, but you need to "late bind" and loose on some performance/debugging features.

来源: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

以下是我的尝试:

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

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

虽然我们可以找到一些关于什么是支持的来源，但知道哪些元字符等是不支持的将是有帮助的。更深入的解释可以在这里找到。该资料中提到:

"Although "VBScript正则表达式…5.5版本实现了很多基本的正则表达式特性，这些特性在以前版本的VBScript. ...中是没有的JavaScript和VBScript实现了perl风格的正则表达式。然而，它们缺乏Perl和其他现代正则表达式中提供的大量高级特性:

因此，不支持的有:

Start of String ancor \A, alternatively use the ^ caret to match postion before 1st char in string End of String ancor \Z, alternatively use the $ dollar sign to match postion after last char in string Positive LookBehind, e.g.: (?<=a)b (whilst postive LookAhead is supported) Negative LookBehind, e.g.: (?<!a)b (whilst negative LookAhead is supported) Atomic Grouping Possessive Quantifiers Unicode e.g.: \{uFFFF} Named Capturing Groups. Alternatively use Numbered Capturing Groups Inline modifiers, e.g.: /i (case sensitivity) or /g (global) etc. Set these through the RegExp object properties > RegExp.Global = True and RegExp.IgnoreCase = True if available. Conditionals Regular Expression Comments. Add these with regular ' comments in script

我已经在VBA中使用正则表达式不止一次地碰壁了。通常使用LookBehind，但有时我甚至忘记修饰符。我自己没有经历过上面提到的所有这些背景，但我认为我应该参考一些更深入的信息。请随意评论/更正/补充。正则表达式.info提供了丰富的信息。

附注:你提到了常规的VBA方法和函数，我可以确认它们(至少对我自己)在RegEx失败的地方以自己的方式提供了帮助。

为那些赶时间的人扩展patszim的答案。

Open Excel workbook. Alt+F11 to open VBA/Macros window. Add reference to regex under Tools then References and selecting Microsoft VBScript Regular Expression 5.5 Insert a new module (code needs to reside in the module otherwise it doesn't work). In the newly inserted module, add the following code: 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 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