MAC APP FOR TESTING REGULAR EXPRESSIONS CODE

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.

MAC APP FOR TESTING REGULAR EXPRESSIONS 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.

Mac app for testing regular expressions