Using Regular Expressions for sub strings in Excel

Ever wanted to extract a sub string from a cell in Microsoft Excel using a Regular expression? Here’s a Visual Basic snippet that will do just that.

Function getRegexGroup(inRegex As String, inValue As String, inGroup As Integer)
    Set myRegExp = CreateObject("vbscript.regexp")
    myRegExp.IgnoreCase = True
    myRegExp.Global = True
    myRegExp.Pattern = inRegex
    Set myMatches = myRegExp.Execute(inValue)
    Set SubMatches = myMatches.Item(0).SubMatches
 
    getRegexGroup = SubMatches.Item(inGroup)
End Function

You can use it like so:

Extract Regular Expression Group in Excel

=getRegexGroup([REGULAR EXPRESSION], [INPUT STRING], [GROUP NUMBER TO EXTRACT])

2 comments on “Using Regular Expressions for sub strings in Excel

  1. Hi Rob. Love the snippet. If you could please help with the following, it will truly end my search, my reading, my trials and errors.

    If we had “Hello Bob it’s Tim. Ah yes, Hello Tim it’s Bob” in one cell.
    Is there a way to get Bob, after the word Hello, and Tim after the second word Hello.

    Can we modify your code to get all iterations of our sub string.

    Thanks in advance.

    Rex

Leave a Reply