Using Regex Functions in Excel

This is a public service message.

The other day some folks in the office were asking how to tell if a certain character appeared in a cell in Excel.  I was shocked when the traditional VB function failed miserably.  The call of instr(1, “blub”, “b”) worked fine from the immediate window… but =instr(1,A1,”b”) would die in a cell.  Was my face red!  The computer “genius” sure looked like a chump just then.

Never again, I tell you.  Never again.

Allright, so you want to be able to do this:

Matching with regex

To make it happen, hit alt-F11 to go to the code screen.  Click “Insert | Module” to add a new code module.  Then click”Tools | References” so you can check the “Microsoft VBScript Regular Expressions 5.5” box that will make this work:

set those references!

Now paste in this code in your module and you’re good to go!

  
Function M(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
        M = "Matches '" & Pattern & "'"
    Else
        M = ""
    End If
End Function
  
Function StartsWith(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = "^" & Pattern
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
        StartsWith = "Starts with '" & Pattern & "'"
    Else
        StartsWith = ""
    End If
End Function

Function EndsWith(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern & "$"
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
        EndsWith = "Ends with '" & Pattern & "'"
    Else
        EndsWith = ""
    End If
End Function

Function S(Value As String, Pattern As String, ReplaceWith As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = IgnoreCase
    r.Global = True
    S = r.Replace(Value, ReplaceWith)
End Function