如何通过函数或自定义函数在excel中获得正则表达式支持？ [英] How do I get regex support in excel via a function, or custom function?

问题描述

除了通过VBA外，似乎在excel中不支持正则表达式（如正则表达式中）。是这样的，如果是，是否有任何支持正则表达式的开源定制VBA功能。在这种情况下，我希望在字符串中提取复杂的模式，但是在函数本身内容中可以使用暴露对正则表达式支持的自定义VBA函数的任何实现。如果您知道半相关功能，例如 IS 函数，感觉到评论，虽然我真的正在寻找通过函数公开的完整的正则表达式实现。如果执行情况良好，甚至可以开放付款使用加载项。

It appears that regex (as in regular expressions) is not supported in excel, except via VBA. Is this so, and if it is, are there any "open source" custom VBA functions that support regex. In this case I'm looking to extract complex pattern within a string, but any implementation of a custom VBA function that expose support of regex within the function itself would be of use. If you know of semi-related function such as the IS function, feel feel to comment, though I'm really looking for a full regular expression implementation that is exposed via functions. Might even be open to a pay to use add-in if the implementation is good.

另外，刚刚开始，我在Windows 7上使用Office 2010;添加了这个信息后，似乎是一个伟大的建议，原来是不能在Office 2010上工作。

Also, just a heads up that I'm using Office 2010 on Windows 7; added this info after a answer that appears to be a great suggestion turned out not to work on Office 2010.

如果您有任何问题，请评论。

If you have questions, please comment.

推荐答案

没有内置到Excel中。 VBScript具有内置支持，可以从VBA调用。更多信息此处提供。您可以使用VBA中的后期绑定调用对象。我已经包括我最近组合的几个功能。请注意，这些没有经过很好的测试，可能有一些错误，但它们非常简单。

Nothing built into Excel. VBScript has built-in support and can be called from VBA. More info available here. You can call the object using late binding in VBA. I've included a few functions that I put together recently. Please note that these are not well-tested and may have some bugs, but they are pretty straightforward.

这至少应该让你开始：

'---------------------------------------------------------------------------------------vv
' Procedure : RegEx
' Author    : Mike
' Date      : 9/1/2010
' Purpose   : Perform a regular expression search on a string and return the first match
'               or the null string if no matches are found.
' Usage     : If Len(RegEx("\d{1,2}[/-]\d{1,2}[/-]\d{2,4}", txt)) = 0 Then MsgBox "No date in " & txt
'           : TheDate = RegEx("\d{1,2}[/-]\d{1,2}[/-]\d{2,4}", txt)
'           : CUSIP = Regex("[A-Za-z0-9]{8}[0-9]",txt)
'---------------------------------------------------------------------------------------
'^^
Function RegEx(Pattern As String, TextToSearch As String) As String 'vv
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = False
        .Pattern = Pattern
    End With

    Set REMatches = RE.Execute(TextToSearch)
    If REMatches.Count > 0 Then
        RegEx = REMatches(0)
    Else
        RegEx = vbNullString
    End If
End Function '^^

'---------------------------------------------------------------------------------------
' Procedure : RegExReplace
' Author    : Mike
' Date      : 11/4/2010
' Purpose   : Attempts to replace text in the TextToSearch with text and back references
'               from the ReplacePattern for any matches found using SearchPattern.
' Notes     - If no matches are found, TextToSearch is returned unaltered.  To get
'               specific info from a string, use RegExExtract instead.
' Usage     : ?RegExReplace("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My phone # is 570.555.1234.", "$1($2)$3-$4$5")
'             My phone # is [phone].
'---------------------------------------------------------------------------------------
'
Function RegExReplace(SearchPattern As String, TextToSearch As String, ReplacePattern As String, _
                      Optional GlobalReplace As Boolean = True, _
                      Optional IgnoreCase As Boolean = False, _
                      Optional MultiLine As Boolean = False) As String
Dim RE As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = MultiLine
        .Global = GlobalReplace
        .IgnoreCase = IgnoreCase
        .Pattern = SearchPattern
    End With

    RegExReplace = RE.Replace(TextToSearch, ReplacePattern)
End Function

'---------------------------------------------------------------------------------------
' Procedure : RegExExtract
' Author    : Mike
' Date      : 11/4/2010
' Purpose   : Extracts specific information from a string.  Returns empty string if not found.
' Usage     : ?RegExExtract("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My phone # is 570.555.1234.", "$2$3$4")
'             5705551234
'             ?RegExExtract("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My name is Mike.", "$2$3$4")
'
'             ?RegExReplace("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My name is Mike.", "$2$3$4")
'             My name is Mike.
'---------------------------------------------------------------------------------------
'
Function RegExExtract(SearchPattern As String, TextToSearch As String, PatternToExtract As String, _
                      Optional GlobalReplace As Boolean = True, _
                      Optional IgnoreCase As Boolean = False, _
                      Optional MultiLine As Boolean = False) As String
Dim MatchFound As Boolean

    MatchFound = Len(RegEx(SearchPattern, TextToSearch)) > 0
    If MatchFound Then
        RegExExtract = RegExReplace(SearchPattern, TextToSearch, PatternToExtract, _
                                    GlobalReplace, IgnoreCase, MultiLine)
    Else
        RegExExtract = vbNullString
    End If
End Function

这篇关于如何通过函数或自定义函数在excel中获得正则表达式支持？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！