这不是一个直接的答案，但可能会为你提供一个更有效的选择。这就是谷歌表有几个内置的正则表达式函数，这些可以非常方便，并帮助绕过Excel中的一些技术程序。显然，在个人电脑上使用Excel有一些优势，但对于大多数用户来说，谷歌表格将提供相同的体验，并可能在文档的可移植性和共享方面提供一些好处。

他们提供

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

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

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

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组件感到畏惧的用户提供了一个简单的解决方案。

我需要使用它作为一个单元格函数(如SUM或VLOOKUP)，并发现它很容易:

Make sure you are in a Macro Enabled Excel File (save as xlsm). Open developer tools Alt + F11 Add Microsoft VBScript Regular Expressions 5.5 as in other answers Create the following function either in workbook or in its own module: 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 Then you can use in cell with =REGPLACE(B1, "(\w) (\d+)", "$1$2") (ex: "A 243" to "A243")

为那些赶时间的人扩展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

为了增加有价值的内容，我想创建一个关于为什么有时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失败的地方以自己的方式提供了帮助。

以下是我的尝试:

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

我不想启用一个参考库，因为我需要我的脚本是可移植的。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