How do I get regex support in Excel via a function, or custom function?

by

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.

This should at least get you started:

'---------------------------------------------------------------------------------------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 (570)555-1234.
'---------------------------------------------------------------------------------------
'
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

More Related Contents:

  1. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  2. Returning a regex match in VBA (excel)
  3. Splitting String in VBA using RegEx
  4. RegEx with Excel VBA on Mac
  5. html parsing of cricinfo scorecards
  6. How to Use/Enable (RegExp object) Regular Expression using VBA (MACRO) in word
  7. Lookbehind on regex for VBA?
  8. Excel Number Separation [closed]
  9. VBA Stuck after certain loop
  10. Is the . in .Range necessary when defined by .Cells?
  11. How to return a result from a VBA function
  12. How can I validate an email address using a regular expression?
  13. Regular Expression Arabic characters and numbers only
  14. How to match, but not capture, part of a regex?
  15. Properly Handling Errors in VBA (Excel)
  16. RegEx to match full string
  17. How to print matched regex pattern using awk?
  18. Regex that does not allow consecutive dots
  19. VBA – How the colon `:` works in VBA code with condition
  20. Click button or execute JavaScript function with VBA
  21. Comparison of Dictionary, Collections and Arrays
  22. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  23. regex: find one-digit number
  24. How to change date format in sed?
  25. Find and FindNext for Excel VBA
  26. VBA:IE-How to assign pathname to file input tag without popup file upload form?
  27. Is there any way to put malicious code into a regular expression?
  28. Replace all whitespace with a line break/paragraph mark to make a word list
  29. Why do regex engines allow / automatically attempt matching at the end of the input string?
  30. Apache mod_rewrite REDIRECT_STATUS condition causing directory listing

Leave a Comment