[VBA] How to Extract Matches and Values Using Regular Expressions

Overview

Using Regular Expressions (RegExp) in Excel VBA allows you to easily extract strings that match specific patterns. In this article, I will explain how to retrieve matched values and their partial matches (groups) using the Matches collection and SubMatches property.

Sample Code

Sub ExtractMatchesWithRegExp()
    Dim regObj As Object
    Dim matches As Object
    Dim i As Long
    
    ' Create Regular Expression Object
    Set regObj = CreateObject("VBScript.RegExp")
    regObj.Global = True
    regObj.Pattern = "\d+" ' Match one or more digits
    
    ' Execute matching
    Set matches = regObj.Execute("9876-5432")
    
    ' Output count and individual match results
    Range("B2").Value = matches.Count
    Range("B3").Value = matches(0).Value
    Range("B4").Value = matches(1).Value
    
    ' Get partial matches using grouped patterns
    regObj.Pattern = "(\d+)-(\d+)"
    Set matches = regObj.Execute("9876-5432")
    
    Range("E2").Value = matches.Count
    With matches(0)
        Range("E3").Value = .SubMatches(0) ' Number before hyphen
        Range("E4").Value = .SubMatches(1) ' Number after hyphen
    End With
End Sub

Explanation of the Code

Creating the RegExp Object

Use CreateObject("VBScript.RegExp") to generate a Regular Expression object.

Global Property

When set to True, the object targets all matches within the string, not just the first one.

Pattern Property

Specifies the pattern to extract.

• \d+: Matches one or more continuous digits.
• (\d+)-(\d+): Groups two sets of digits separated by a hyphen.

Execute Method

Applies the regular expression to the target string and returns the result as a Matches collection.

Accessing the Matches Collection

You can retrieve individual matched strings using syntax like matches(0).Value.

SubMatches Collection

You can retrieve the parts enclosed in parentheses () within the pattern. Example: For (\d+)-(\d+), the value of the first set of parentheses is .SubMatches(0), and the second is .SubMatches(1).

Application Examples

• Splitting a phone number into the area code and subscriber number.
• Processing postal codes by splitting them into the first and second halves.
• Extracting the category part and number part from a product code.

Summary

VBA Regular Expressions allow you to do more than just search or replace; you can handle match results as a collection for detailed data extraction.

By utilizing Matches and SubMatches, you can significantly improve the efficiency of string analysis and data organization.