Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

4 Answers 4

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

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

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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