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?


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
| improve this answer | |
  • Can you explain how you add a reference? – Chris Walsh Nov 29 '18 at 16:38

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

| improve this answer | |
  • It works great indeed (I'm using Excel 2013) – Sébastien Nov 25 '13 at 14:02

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.

| improve this answer | |
  • Thanks for the confirmation. I found that library, but we were hoping to have something that didn't require us to distribute a library, too. – Erick Robertson Aug 26 '10 at 15:04
  • @Erick, yep, I hate adding addins to my spreadsheets, a big pain to keep track of them. Unless you need complete regex flexibility, you can probably get the job done with other functions. There's a lot of tricks in Excel. Post specific requests here. – Lance Roberts Aug 26 '10 at 15:16

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.