Regular Expressions

Regular expressions (often called regex or regexp) are patterns used to match, search and manipulate text.
They provide a compact way to describe text structures such as email addresses, dates, numbers, words, or any other textual pattern.

link - regexone.com 

VBA Built-in Classes

Starting with Office 365, Version 2508, There are four built-in classes that can handle regular expressions.
RegExp - The main regular expression engine (pattern, options, execution).
Match - Represents a single match result.
MatchCollection - A collection of all matches returned by a RegExp execution.
SubMatches - A collection of captured groups (parentheses) within a match.
These are the same objects as the VBScript libray except they are built-in to VBA so you do not need any external reference.

Dim rx As RegExp 
Set rx = New RegExp 

rx.Pattern = "\d+" 
rx.Global = True 

Dim matches As MatchCollection 
Set matches = rx.Execute("A1 B22 C333") 

Dim m As Match 
For Each m In matches 
    Debug.Print m.Value 
Next 

Microsoft VBScript Regular Expressions 5.5

In earlier versions you had to add a reference to an additional library.

Dim oRegEx As VBScript_RegExp_55.RegExp 
Dim oRegExMatch As VBScript_RegExp_55.MatchCollection 
Set oRegEx = CreateObject("VBScript.RegExp") 

link - devblogs.microsoft.com/microsoft365dev/how-to-prepare-vba-projects-for-vbscript-deprecation/ 
link - learn.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/scripting-articles/ms974570(v=msdn.10) 

Code Snippets

Dim sTextString As String 

Dim oRegEx As RegExp 
Dim oRegExResults As Object 
Set oRegEx = New RegExp 

oRegEx.Global = True 
oRegEx.MultiLine = True 
oRegEx.IgnoreCase = False 

'replacing text
Dim sNewString As String 
sTextString = "bettersolutions" 
oRegEx.Pattern = "better" 
sNewString = oRegEx.Replace(sTextString, "amazing") 
Debug.Print sNewString 

'testing for a match, 2 digit numbers
Dim bfound As Boolean 
sTextString = "1,2,30,4,5" 
oRegEx.Pattern = "\d{2}" 
bfound = oRegEx.Test(sTextString) 
If (bfound = True) Then 
   Debug.Print "match found" 
End If 

extracting all instances 
Dim oFound As Object 
sTextString = "one,two,three,four" 
oRegEx.Pattern = "[a-z]+"           
Set oRegExResults = oRegEx.Execute(sTextString) 
For Each oFound In oRegExResults 
   Debug.Print oFound 
Next oFound 

Matching Exact 2 Characters

Set oRegEx = New VBScript_RegExp_55.RegExp 
sTextString = "one,two,three,four" 
oRegEx.Pattern = "ee" 
Set oRegExMatch = oRegEx.Execute(sTextString) 
Debug.Print pRegExMatch.Count 

Matching Any 3 Digits

The "\d" can match any digit, equivalent to [0-9]

oRegEx.Pattern = "\d\d\d" 

Matching Any 3 Characters

The "." will match any letter, digit, whitespace, everything except \n

oRegEx.Pattern = "..." 

Matching Any 3 Non Digits

The "\D" will match any non digit, equivalent to [^0-9]

oRegEx.Pattern = "\D" 

Matching A Full Stop

The "\." will match a full stop

oRegEx.Pattern = "\." 

Matching Certain Characters

The "[et]" will match single "e" and "t" characters

oRegEx.Pattern = "[et]" 

Matching Excluding Characters

The "[^et]" will match everything that doesn't contain the single characters "e" and "t"

oRegEx.Pattern = "[^et]" 

Matching Any Alphanumeric Characters

The "\w" will match all letters and numbers, equivalent to [A-Za-z0-9]

oRegEx.Pattern = "\w" 

Matching Any 3 Letters with the first one in uppercase

oRegEx.Pattern = "[A-Z][a-za-z]" 

Matching Repetition

This will match "aaaa"

oRegEx.Pattern = "a{4}" 

This will match ??

oRegEx.Pattern = "[abc]{2}" 

This will match any characters repeated 2,3 or 4 times

oRegEx.Pattern = ".{2,4}" 

Matching Zero or More Repetitions

The "*" will match zero or more repetitions

oRegEx.Pattern = "\d*" 

This will match any number of a's, b's and c's

oRegEx.Pattern = "a*b*c*" 

Matching One or More Repetitions

The "+" will match one or more repetitions

oRegEx.Pattern = "\d+" 

Matching Optional Characters

The "?" will match optional characters
This will make the "b" an optional character

oRegEx.Pattern = "ab?c" 

Matching Whitespace

The "\s" will match any single space, tab character, new line or carriage return character

oRegEx.Pattern = ".\.\s+abc" 

Matching Start and End

oRegEx.Pattern = "^error$"