Call regex in an Excel cell

Question

I want to extract a string from a cell using a regex match. I can't find the excel function that does it and I'm no VBA expert either. I use Excel 2007.

Answer 1

In the Excel VBA code editor select Tools/References. In the picker check the latest version of Microsoft VBScript Regular Expresions.

Here is a simple user defined function to get you started. This will return "foo" if you point it at a cell with "foobar" or "fooooobar" in it.

Function RegExtract(contents As String)
    Dim regx As RegExp
    Set regx = New RegExp

    With regx
    .Pattern = "f[o]+"
        With .Execute(contents)
            If .Count Then RegExtract = .Item(0).Value
        End With
    End With
End Function

Answer 2

Here's a function that will let you pass in a cell then any regex pattern. Use () around the part of the pattern you want the function to return.

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String) As String

Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)
RegexExtract = allMatches.Item(0).submatches.Item(0)

End Function

Answer 3

@AndrewCowenhoven Has a good answer. It's simple to add the Regex Library. Just to expand this answer a little, I will add what I learned to get items within matches.

For example, I have a string like this in one cell.

doubleclick.net/activityi;u=5f2256de37ab4992927b3a0a0a34f983;u13=;u14=27.34;u16=AUD;u9=;u10=;u11=;u12=;u5=;u6=;u7=;u8=;u1=Car;u2=SEA;u3=SEA;u4=20130923%20%7C%2020130926;ord=5360319407191.128?

I only need a few sections to verify that the doubleclick tag sent to google is correct. Using The regex shown above I have something like this.

Function CheckU3(contents As String)
Dim regx As RegExp
Set regx = New RegExp
Dim matches, s

With regx
.Pattern = "u3=(.*?);"
.Global = False

If regx.Test(contents) Then
    Set matches = regx.Execute(contents)
        For Each Var In matches.Item(0).SubMatches
            s = Var
        Next
End If

CheckU3 = s

End With
End Function

I know this could be simplified a lot, but the point is that I'm pulling the submatches using parans to get specifically what I need.

Answer 4

There's a library available in Excel's VBA system known as Microsoft VBScript Regular Expressions. Once you include the library from the internal VBA 'IDE', you can create a simple macro that accepts whatever value you'd like to parse and return the result back to the caller (which can be a function call from a cell).