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

Answer 5

Here's how I did a regex replace to extract the number(s) from a cell:

Function REGREP(value As Range, searchString As String, replaceString As String,
  Optional ignoreCase As Boolean = True, Optional searchGlobal As Boolean = True)
  'Dim reg As New RegExp
  Dim reg As Object
  Set reg = CreateObject("vbscript.regexp")
  reg.ignoreCase = ignoreCase
  reg.Global = searchGlobal
  reg.Pattern = searchString
  REGREP = reg.replace(value.value, replaceString)
End Function
Function ASNUM(value As Range)
  ASNUM = REGREP(value, "[^0-9.]+", "")
End Function

'Usage:
REGREP(A1, "[^0-9.]+", "")
ASNUM(A1)

Note that you first need to open up Visual Basic inside Excel using Alt + F11. Then click Tools -> References, scroll down to Microsoft VBScript Regular Expressions 5.5, and add it in. Then RegExp shows up when you type it.

You need to save the file as an Excel Macro-Enabled Workbook (*.xlsm), in order to keep the macro... You can also export and import your code Module.