[regex] How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation?

• In-cell function to return a matched pattern or replaced value in a string.
• Sub to loop through a column of data and extract matches to adjacent cells.
• What setup is necessary?
• What are Excel's special characters for Regular expressions?

I understand Regex is not ideal for many situations (To use or not to use regular expressions?) since excel can use Left, Mid, Right, Instr type commands for similar manipulations.

Here is a regex_subst() function. Examples:

=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo

Here is the simplified code (simpler for me, anyway). I couldn't figure out how to build a suitable output pattern using the above to work like my examples:

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

I needed to use this as a cell function (like SUM or VLOOKUP) and found that it was easy to:

1. Make sure you are in a Macro Enabled Excel File (save as xlsm).
2. Open developer tools Alt + F11
3. Add Microsoft VBScript Regular Expressions 5.5 as in other answers

4. 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
   

5. Then you can use in cell with =REGPLACE(B1, "(\w) (\d+)", "$1$2") (ex: "A 243" to "A243")

I don't want to have to enable a reference library as I need my scripts to be portable. The Dim foo As New VBScript_RegExp_55.RegExp line caused User Defined Type Not Defined errors, but I found a solution that worked for me.

Update RE comments 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.

Source: https://peltiertech.com/Excel/EarlyLateBinding.html

What you'll want to do is put an example string in cell A1, then test your strPattern. Once that's working adjust then rng as desired.

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

Here is my attempt:

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

Expanding on patszim's answer for those in a rush.

1. Open Excel workbook.
2. Alt+F11 to open VBA/Macros window.
3. Add reference to regex under Tools then References
   
4. and selecting Microsoft VBScript Regular Expression 5.5
   
5. Insert a new module (code needs to reside in the module otherwise it doesn't work).
   
6. In the newly inserted module,
   

7. 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
   

8. 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

This isn't a direct answer but may provide a more efficient alternative for your consideration. Which is that Google Sheets has several built in Regex Functions these can be very convenient and help circumvent some of the technical procedures in Excel. Obviously there are some advantages to using Excel on your PC but for the large majority of users Google Sheets will offer an identical experience and may offer some benefits in portability and sharing of documents.

They offer

REGEXEXTRACT: Extracts matching substrings according to a regular expression.

REGEXREPLACE: Replaces part of a text string with a different text string using regular expressions.

SUBSTITUTE: Replaces existing text with new text in a string.

REPLACE: Replaces part of a text string with a different text string.

You can type these directly into a cell like so and will produce whatever you'd like

=REGEXMATCH(A2, "[0-9]+")

They also work quite well in combinations with other functions such as IF statements like so:

=IF(REGEXMATCH(E8,"MiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*")/1000,IF(REGEXMATCH(E8,"GiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*"),"")

Hopefully this provides a simple workaround for users who feel taunted by the VBS component of Excel.

To make use of regular expressions directly in Excel formulas the following UDF (user defined function) can be of help. It more or less directly exposes regular expression functionality as an excel function.

How it works

It takes 2-3 parameters.

1. A text to use the regular expression on.
2. A regular expression.
3. A format string specifying how the result should look. It can contain $0, $1, $2, and so on. $0 is the entire match, $1 and up correspond to the respective match groups in the regular expression. Defaults to $0.

Some examples

Extracting an email address:

=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")

Results in: [email protected]

Extracting several substrings:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Results in: E-Mail: [email protected], Name: Peter Gordon

To take apart a combined string in a single cell into its components in multiple cells:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

Results in: Peter Gordon [email protected] ...

How to use

To use this UDF do the following (roughly based on this Microsoft page. They have some good additional info there!):

1. In Excel in a Macro enabled file ('.xlsm') push ALT+F11 to open the Microsoft Visual Basic for Applications Editor.
2. Add VBA reference to the Regular Expressions library (shamelessly copied from Portland Runners++ answer):
   1. Click on Tools -> References (please excuse the german screenshot)
   2. Find Microsoft VBScript Regular Expressions 5.5 in the list and tick the checkbox next to it.
   3. Click OK.

3. 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).

   

4. 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
   

5. Save and close the Microsoft Visual Basic for Applications Editor window.

To add to the valuable content, I would like to create this reminder on why sometimes RegEx within VBA is not ideal. Not all expressions are supported, but instead may throw an Error 5017 and may leave the author guessing (which I am a victim of myself).

Whilst we can find some sources on what is supported, it would be helpfull to know which metacharacters etc. are not supported. A more in-depth explaination can be found here. Mentioned in this source:

"Although "VBScript’s regular expression ... version 5.5 implements quite a few essential regex features that were missing in previous versions of VBScript. ... JavaScript and VBScript implement Perl-style regular expressions. However, they lack quite a number of advanced features available in Perl and other modern regular expression flavors:"

So, not supported are:

• 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

I already hit a wall more than once using regular expressions within VBA. Usually with LookBehind but sometimes I even forget the modifiers. I have not experienced all these above mentioned backdrops myself but thought I would try to be extensive referring to some more in-depth information. Feel free to comment/correct/add. Big shout out to regular-expressions.info for a wealth of information.

P.S. You have mentioned regular VBA methods and functions, and I can confirm they (at least to myself) have been helpful in their own ways where RegEx would fail.