#Test regex code#

You can also “capture” parts of the patterns as so called “Submatches”.ĭebug.Print match.Value 'Result: 123-000, 123-643, 123-888Īs you can see we have managed to capture 3 instances of the 123-+ pattern in the string. The Execute function will match the first or all instances of a certain pattern within a certain string. Global = True 'If False, would replace only first The Replace function will replace the first (if Global = False) or all matching patterns (if Global = True) within a certain string with another string of your choosing. The Test function allows you to test whether the selected Pattern provides any match against the string.ĭebug.Print regex.Test(str) 'Result: Trueĭebug.Print regex.Test(str) 'Result: False

Execute (search-string) – returns all matches of the pattern against the search-string.

Replace (search-string, replace-string) – replaces occurrences of the pattern in search-string with replace-string.

Test (string) – returns True if the pattern can be matched agaist the provided string.

The RegExp object facilitates the following 3 operations:

MultiLine – Do you want to match the pattern across line breaks?.

Set false if you need just the first match for performance

Global – Do you want to find all possible matches in the input string? If false, only match the first found pattern.

IgnoreCase – Ignore letter case (captial/non-capital letters).

Pattern – The pattern (written in Regex) which you want to match against (e.g.

The RegExp object has the following properties: I personally prefer using the CreateObject function as it does not require referencing the library every time the Workbook is opened on a new workstation. Option 2: Using the CreateObject function Option 1: Referencing the library Microsoft VBScript Regular Expressions Otherwise, if you don’t want to reference this library every time you can also create the RegExp object using the CreateObject function. To start using this object add the following reference to your VBA Project: Tools-> References-> Microsoft VBScript Regular Expressions. To use Regex in VBA you need to use the RegExp object which is defined in the Microsoft VBScript Regular Expressions library. Group BUT DON’T CAPTURE the expression within the parenthesis ( ) Group and capture the expression within the parenthesis ( )Ĭaptures “123, “345” and “789” within “123-456-789” Matches as few as possibleīelow the basic grouping expressions: Syntax Matches as few as possibleīetween “n” and “m” times (non-GREEDY).

Matches as few as possibleĪt least “n” times (non-GREEDY). It is important to understand the difference between GREEDY and non-GREEDY quantifiers: Quantifiers allow you to specify the amount of times a certain pattern is supposed to matched against a string. “I”, “a” “m” “J” “o” “h” “n” in “I_am.John”įollowed by any special character – escapes special characters Matches any character belonging to the character range specified between brackets Īny word character (letters, modifiers, digits, punctuation and connectors) Matches any character not being one of the provided between brackets Matches any provided character between brackets Below a quick reference: Matching characters Syntax The Regular Expression language (Regex) is quite elaborate but allows you to match virtually any regular language.

#Test regex code#

As you can see a Regex uses a certain code to translate your pattern.

What does the + pattern represent? It translates to the following: capture any pattern matching the following range of characters (), being numbers from 0-9, in a sequence of at least 1 or more (+). It returns True as string pattern was found Regex.Pattern = '+' 'Pattern - look for sequence of numbers Set regex = CreateObject('VBScript.RegExp') 'RegExp object initiation without referencing library Below a simple example where we check if the pattern exists in the string. Without knowing ahead how the text looks like it would be hard to extract these numbers both using Excel Functions and VBA. Let’s demonstrate this with a simple Regex example. A Regex (Regular Expression) is basically a pattern matching strings within other strings.