Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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
share|improve this answer

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

share|improve this answer
    
It works great indeed (I'm using Excel 2013) – Sebastien 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.

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .