Can you do Regular Expressions in Excel without VBScript?

Question

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to the spreadsheet for this because I can hard-code a solution faster. It's just that the fastest solution would be a function. I can't find one, though. So maybe there's nothing.

Anyone know?

Answer 1

You can simply add a reference to 'Microsoft VBScript Regular Expressions 5.5' in the VBE to expose the VBScript.dll regex functions to Excel. Writing a simple regex function is then trivial, e.g.

Public Function emailCheck(rawEmail As String) As Boolean
    Dim reg As New RegExp
    reg.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,4}$"
    emailCheck = False
    If reg.Test(rawEmail) Then
        emailCheck = True
    End If
End Function

Answer 2

The .xla package at this site worked very well for me.

Answer 3

MoreFunc.xll is a free addin that has Regex functionality.

There are some other third-party addins that can do Regex also.

There are no true native worksheet functions to do it, but if you have a specific search to do you can probably use other functions to get the job done.

Answer 4

Here's a function that gives the number of regular expression matches within a particular cell:

Public Function regexCount(regex As String, ref As Range) As Integer
    Dim reg As New RegExp
    reg.Global = True
    reg.Pattern = regex
    regexCount = reg.Execute(ref.Value).Count
End Function

(Requires adding a reference to 'Microsoft VBScript Regular Expressions 5.5' - just go to 'Tools' --> 'References' from the VBA editor and check the checkbox for that reference.)