25

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?

5 Answers 5

13

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
3
  • Can you explain how you add a reference? Nov 29, 2018 at 16:38
  • @ChrisWalsh Looks like the answer is to open up Visual Basic inside Excel using Alt + F11. Then click Tools -> References, scroll down to his exact quote, and add it in. Then RegExp shows up when you type it.
    – Andrew
    Jun 2, 2023 at 21:16
  • So it turns out that you can actually use Dim reg As Object and then Set reg = CreateObject("vbscript.regexp") instead of Dim reg As New RegExp and having to manually add the reference.
    – Andrew
    Jun 2, 2023 at 21:58
8

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

1
  • It works great indeed (I'm using Excel 2013)
    – Sébastien
    Nov 25, 2013 at 14:02
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.

2
  • 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. Aug 26, 2010 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. Aug 26, 2010 at 15:16
3

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

-1

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.

3
  • Please note that the title of the question sid "without VBScript". Jun 2, 2023 at 22:07
  • @AndrewMorton While that is true, the answer is it is not possible. Therefore, this is the best possible alternative, as it lets you as easily as possible add a regex function to your Excel spreadsheet. Thanks for the downvote though.
    – Andrew
    Jun 3, 2023 at 4:03
  • @AndrewMorton Ah, I see you're a "programmer for many years in mostly VB.NET". Extra thanks then!
    – Andrew
    Jun 3, 2023 at 4:04

You must log in to answer this question.

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