VBA Regex

Associated Files Download Links

What is Regex?

Regex stands for regular expression. A regular expression is a pattern made up of a sequence of characters that you can use to find a matching pattern in another string. In order to use Regex in VBA you have to use the RegExp object.

A pattern such as [A-C] can be used to search for and match an upper case letter from A to C from a sequence. Regex patterns have their own syntax and can be built using a character or sequence of characters.

Matching Characters

The following table shows the syntax which will allow you to build Regex patterns.

Pattern Syntax Description Example Matches Found
. Matches any single character except vbNewLine f.n fan, fon, f@n, fwn
[characters] Matches any single character between brackets[] [fn] Would only match “f” or “n” in fan
[^characters] Matches any single character that is not between brackets[] [^fn] So would match “j” in “fjn”
[start-end] Matches any character that is part of the range in brackets[] [1-5] Would match “4” and “5” in “45”
\w Matches alphanumeric characters and the underscore, but not the space character \w Would match “c” in “,c.”
\W Matches any non-alphanumeric characters and the underscore \W Would match “@” in “bb@bb”
\s Matches any white space character such as spaces and tabs \s Would match ” ” in “This is”
\S Matches any non-white space character \S Would match “T” and “h” in “T h”
\d Matches any single decimal digit \d Would match “7” in “a7h”
\D Matches any single non-decimal digit \D Would match j in “47j”
\ Escapes special characters which then allows you to search for them \. Would match “.” in “59.pQ”
\t Tab \t Would match a tab character
\r Carriage Return \r Would match a carriage return (vbCr)
\n vbNewLine(vbTab) \n Would match a new line

Quantifiers

You can use quantifiers to specify how many times you want the pattern to match against the string.

Quantifier Description Example Matches Found
* Matches zero or more occurrences fn*a  fna, fa, fnna, fnnna, fnfnnna
+ Matches one or more occurrences fn+a fna, fnna, fnfnna
? Matches zero or one fn?a fa, fna
{n} Matches “n” many times d\W{4} Would match “d….” in “d….&5hi”
{n,} Matches at least “n” number of times d\W{4,} Would match “d….&” in “d….&5hi”
{n,m} Matches between n and m number of times d\W{1,8} Would match “d….&&&&” in “d….&&&&5hi”

Grouping

Grouping or capturing allows you to use a pattern to capture and extract a portion of a string. So not only is the pattern matched, but the part of the string that matches the pattern is captured.

Pattern Description Example Matches Found and Captured
(expression) Groups and captures the pattern in parenthesis (\W{4}) Would group and capture “@@@@” from “1@@@@1jlmba”

How to Use Regex in VBA

In order to use Regex in VBA, you first have to set the reference in the VBE editor. In the VBE editor, go to Tools>References>Microsoft VBScript Regular Expressions.

Adding the Reference For the Regex Expressions in VBA

These are the properties of the RegExp object:

  • Pattern – The pattern you are going to use for matching against the string.
  • IgnoreCase – If True, then the matching ignores letter case.
  • Global – If True, then all the matches of the pattern in the string are found. If False then only the first match is found.
  • MultiLine – If True, pattern matching happens across line breaks.

These are the methods of the RegExp object:

  • Test – Searches for a pattern in a string and returns True if a match is found.
  • Replace – Replaces the occurrences of the pattern with the replacement string.
  • Execute – Returns matches of the pattern against the string.

Testing a Pattern for a Match Against a String

You can use the Test method to check whether a pattern matches a sequence in the input string. The result is True if a match is found. The following code will show you how to test a pattern against a string:

The result is:

Using Regex to Test a Pattern in VBA

Replacing a Pattern in a String

You can use the Replace method to replace the first instance of a matching pattern in a string or all the instances of a matching pattern in a string. If Global is set to False, then only the first instance is replaced. The following code will show you how to replace a pattern in a string:

The result is:

Using Regex to Replace a String in VBA

To replace only the number portion of the string used above, you would use the following code:

The result is:

Replacing a number portion of a String in Regex in VBA

To replace every instance of a certain pattern in a string, you would set the global value to True. The following code shows you how to replace every instance of -A1289C- in the string:

Replacing Every Instance of a String with Regex in VBA

Matching and Displaying a Pattern in a String

You can use the Execute method to match one or all instances of a pattern within a string. The following code shows you how to match and  display all instances of the pattern from the string:

The result is:

Matching and Capturing all the Patterns in a String Using Regex

Let’s say we only wanted to match -ADC- from the above string. The following code shows how to match and display only  -ADC- from the string:

Capturing a Pattern in Regex VBA

Regex can take some time to learn, but it’s an extremely powerful tool for identifying/manipulating strings of text. It’s also broadly used across programming languages.

You may also like some of this related content...

Ads