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

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

以下是我的尝试:

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

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

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