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's in Excel

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!

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
About these ads

6 Responses to “Using Regex Functions in Excel”

  1. Bryan Says:

    I’m not sure how you would do it, but wouldn’t M() be better if it returned a boolean? Then you could do:

    =IF( M( A1, ‘b’ ), “Matches ‘b'”, “Doesn’t match ‘b'” )

    Which doesn’t make your example any better, but would allow enhancements in the future.

  2. Alo Says:

    Of course, you could also have just used the =search() function.

    for your example, you’d find =search(“a”, B2, 1) and if it existed, it would list where it was in the cell, else return an error.

    =if(iserror(search(“a”, B2, 1)),”Does not Exist”,search(“a”, B2, 1))

    Would even tell you it doesn’t exist in that cell if you wanted.

  3. Chad Says:

    This is excellent. Thanks!

  4. Sandra Says:

    If I emailed a sheet containing the formula to someone, would their desktop need to have that VB regex reference installed inorder for the regex portion to still work?

  5. Justin Says:

    Alo,

    The problem with the search function is that it doesn’t support regular expressions like VBScript does. Regular expressions allow you to do a far more advanced search within the screen to get exactely what you want.

  6. David Says:

    Thanks for this – it works well!

    Would there be a way to include variations in spelling within the formula, like for “colour” (color|colour)? I tried but haven’t had any success.

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: