RegEx (Regular Expressions) in Excel

RegEx stands for regular expression and is a sequence of characters that defines a pattern that can be used to match against strings.

Or, in simpler language, I can create a regex pattern and use it to search a string for that pattern. I can also use the regex pattern to validate input.

A simple pattern looks like this

[a-z]

and I can use that pattern to look for any lower case letter from a to z.

You can use VBA string functions and the LIKE operator, and you can use functions like MID, LEFT, RIGHT, in the worksheet. But none of these match RegEx for the complexity of pattern matching and data validation it provides.

Once you've learned RegEx you can use it with a huge range of programming languages, not just in Excel.

RegEx Patterns

RegEx patterns can be simple, or very complicated, but all are built using the following syntax.

Matching Characters

Quantifiers

Quantifiers allow you to modify the basic pattern to specify the number of times you want that pattern to match.

Capturing

We can specify that we want to capture a pattern, which means not only are we matching a pattern against a string, we're saying save that portion of the string that matches our pattern so we can do something with it.

Up to now we've only described matching a pattern against a string, capturing allows us to extract portions of the string. I'll get to examples of that later, first let's look at the syntax.

Anchors

VBA RegEx Methods & Properties

To use RegEx in VBA you'll use the RegExp object. To use this you can either set a reference in the VBA editor, or use the CreateObject function.

I prefer to set a reference as it means IntelliSense works for your new object.

Properties

The RegExp object has these properties:

• Pattern - the pattern used for matching against a string
• IgnoreCase - If True, the matching is case insensitive
• Global - If True, find all matches of the pattern in the string. Otherwise, just the first match is found
• MultiLine - If True, patterns are matched across line breaks in multi-line strings

Methods

The methods we can use are:

• Test - check if the pattern can be found in the string. Returns True if it is found, False if it isn't.
• Execute - returns occurrences of the pattern matched against the string
• Replace - replaces occurrences of the pattern in the string

RegEx Functions

We must write our own functions to use the available RegEx methods.

Download the Example Workbook

These functions and lots of sample patterns are available to download.

Match

The RegExMatch function is the simplest of the three I have written.

You pass in the string to be searched and the pattern. After setting the Pattern property you just call the Test method and return the result, either True or False.

Extract

The RegExExtract function takes 5 arguments:

• Str - the string to be searched
• Pattern - the regex pattern
• MatchIndex - indicates which matched occurrence we want the function to return
• SubMatchIndex - this is optional. Indicates which SubMatch we want the function to return, more on this below
• IgnoreCase- this is optional. If True, indicates we want the matching to be case insensitive. Default is True

Matches and SubMatches

When the Execute method is executed, it can return multiple capturing groups, depending on what pattern is used. Let's look at an example.

If our string is She sells sea shells on the sea shore and our pattern is s(..)(..)(..) then we are trying to match the letter s followed by any 2 characters, another 2 characters, and another 2. Note that this includes spaces.

The parenthesis, (..), indicate that we want to capture these characters into separate groups.

When I execute the pattern s(..)(..)(..) against She sells sea shells on the sea shore I'm storing the result in an object I've called Matches.

This object has a property SubMatches where all the other captured groups are stored.

To access the matches and submatches we use numbers that I'm calling MatchIndex and SubmatchIndex.

Something a little quirky here is that Matches are indexed from 0, which isn't unusual in programming. But SubMatches are indexed from 1.

So if we look at what matches our pattern

and I want to access the 2nd submatch of the 3rd match, I would use Matches(2).SubMatches(2) and this would give me th.

If I just want the 1st match he, I'd use Matches(0).SubMatches(0)

Matches(1).SubMatches(0) gives me s - this includes the space before the s.

Matches(3).SubMatches(2) gives me ho.

Remember that our pattern specifies we are looking to match the letter s followed by 3 sets of 2 characters, but we are only capturing the 3 sets of 2 characters. We're not capturing the s

Consuming the String

As each match is made, RegEx works its way along the string looking for the next match.

What's important to understand is that every time a match is made, the portion of the string that matches is 'consumed' and is then not used to look for the next match.

If we look at our string She sells sea shells on the sea shore the first match is She sel so you might think that the next match is sells s but it's not.

In matching She sel this portion of the string isn't used when looking for the next match. Effectively the next match is looked for in ls sea shells on the sea shore which means the next match is actually s sea s.

Replace

The RegExReplace function takes four arguments:

• Str - the string to be searched
• Pattern - the regex pattern
• ReplaceWith - the string to replace any matches
• ReplaceAll - this is optional. If True it indicates that you want to replace all matches. Default is False.

Practical Uses for RegEx

Now that you know how to use Regex, let put it to some use.

Data Validation

A very common use is to validate data input/format. You can use the RegExMatch function for this, here are some examples.

Replacing Parts of Strings

If the string in cell A1 is ABC-123-XYZ-45-A-6789 I can use RegExReplace to:

Extracting Strings

If the string in cell A1 is IV:INV-32278ABC Widgets PTY LTD I can use RegExExtract to find matches and submatches which enables me to: