Extract pattern from string in Excel using VBA and RegEx

Here in this post I’ll show how to use RegEx (or regular expressions) in VBA Excel to extract a given pattern from string.

Extract patterns from string in Excel using VBA and RegEx

Let us assume I have a string like this:

593 578 xxx 38 sss 384 KKK 44

OR

593-578-xxx-38-sss-384-KKK-44 (patterns separated by hyphen or minus)

The string has random numbers and some patterns like xxx, sss and KKK. I want to extract the patterns only from the string. Like,

xxx
sss
KKK

There two different ways you can do this.

1) Create RegExp Object

In the 1st method I’ll use the CreateObject() method to create a RegExp object.

Option Explicit

Private Sub getPattern()
    Dim rE As Object, str As String
    Set rE = CreateObject("VBScript.RegExp")     ' Create object.
    With rE
        .IgnoreCase = True
        .Global = True
        .Pattern = "([a-zA-Z])\1+"    ' Assign a regex pattern.
    End With
    
    str = "593 578 xxx 38 sss 384 KKK 44"   ' The string to look for the patterns.
    
    ' For the matching patterns.
    Dim myMatches As MatchCollection
    Dim myMatch As Match
    
    Set myMatches = rE.Execute(str)    ' Execute the string
    
    ' Print the patterns from the string.
    For Each myMatch In myMatches
        Debug.Print myMatch.Value
    Next
End Sub

Output

Pattern extracted from a string in Excel using RegEx

2) Add Regular Expressions 5.5 Reference

In the 2nd method, we’ll add a "reference" of Regular Expressions in our VBA project to access methods and properties.

Therefore, 1st add a reference.

In your VBA editor, select Tools from the top menu and click References.

Find Microsoft VBScript Regular Expressions 5.5 library, select it (or check it) and press OK.

Here’s the macro to extract patterns from string.

Option Explicit

Private Sub getPattern()
    Dim str As String
    str = "593 578 xxx 38 sss 384-KKK-44"    ' The string with the patterns

    Dim rE As regExp
    Set rE = New regExp

    Dim myMatches As MatchCollection
    Dim myMatch As Match

    With rE
        .IgnoreCase = True
        .Global = True
        .Pattern = "([a-zA-Z])\1+"    ' Assign a regex pattern.
    End With

    Set myMatches = rE.Execute(str)    ' Execute the string

    For Each myMatch In myMatches
        Debug.Print myMatch.Value
    Next
End Sub

Same result, but different methods.

The methods may be different; however, the RegEx pattern is similar in both the examples.

.Pattern = "([a-zA-Z])\1+"

Let me explain RegExp.

• ([a-zA-Z]): It’s a range for both lower and upper case alphabets from a to z. We are looking for alphabets.

• \1+: Matches and returns the result in a "group". (if you remove this pattern, it will return values separately)

Note: You can test regular expressions here.

← PreviousNext →