我需要使用它作为一个单元格函数(如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")

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

这不是一个直接的答案，但可能会为你提供一个更有效的选择。这就是谷歌表有几个内置的正则表达式函数，这些可以非常方便，并帮助绕过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组件感到畏惧的用户提供了一个简单的解决方案。

以下是我的尝试:

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

下面是一个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

要在Excel公式中直接使用正则表达式，以下UDF(用户自定义函数)可能会有所帮助。它或多或少直接将正则表达式功能暴露为excel函数。

它是如何工作的

它需要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，姓名:Peter Gordon

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

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

结果:彼得·戈登some@email.com…

如何使用

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

In Excel in a Macro enabled file ('.xlsm') push ALT+F11 to open the Microsoft Visual Basic for Applications Editor. Add VBA reference to the Regular Expressions library (shamelessly copied from Portland Runners++ answer): Click on Tools -> References (please excuse the german screenshot) Find Microsoft VBScript Regular Expressions 5.5 in the list and tick the checkbox next to it. Click OK. Click on Insert Module. If you give your module a different name make sure the Module does not have the same name as the UDF below (e.g. naming the Module Regex and the function regex causes #NAME! errors). In the big text window in the middle insert the following: 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 Save and close the Microsoft Visual Basic for Applications Editor window.