RegEx in Excel: using regular expressions in formulas

Characters

These are the most frequently used patterns to match certain characters.

Character classes

Using these patterns, you can match elements of different character sets.

Quantifiers

Quantifiers are special expressions that specify the number of characters to match. A quantifier always applies to the character before it.

Grouping

Grouping constructs are used to capture a substring from the source string, so you can perform some operation with it.

Anchors

Anchors specify a position in the input string where to look for a match.

Alternation (OR) construct

The alternation operand enables the OR logic, so you can match either this or that element.

Look-arounds

Lookaround constructs are helpful when you want to match something that is or isn't followed or preceded by something else. These expressions are sometimes called "zero-width assertions" or "zero-width match" because they match a position rather than actual characters.

Now that you know the essentials, let's move on to the most interesting part - using regexes on real data to parse strings and find the required information. If you need more details about the syntax, the Microsoft guide on Regular Expression Language may prove helpful.

How VBA RegExp functions work

This section explains the inner mechanics and may be interesting to those who want to know exactly what happens at the backend.

To start using regular expressions in VBA, you need to either activate the RegEx object reference library or use the CreateObject function. To save you the trouble of setting the reference in the VBA editor, we chose the latter approach.

The RegExp object has 4 properties:

• Pattern - is the pattern to match in the input string.
• Global - controls whether to find all matches in the input string or just the first one. In our functions, it is set to True to get all matches.
• MultiLine - determines whether to match the pattern across line breaks in multi-line strings or only in the first line. In our codes, it is set to True to search in every line.
• IgnoreCase - defines whether a regular expression is case-sensitive (default) or case-insensitive (set to True). In our case, that depends on how you configure the optional match_case parameter. By default, all the functions are case-sensitive.

VBA RegExp limitations

Excel VBA implements the essential regex patterns, but it does lack many advanced features available in .NET, Perl, Java, and other regex engines. For example, VBA RegExp does not support inline modifiers such as (?i) for case-insensitive matching or (?m) for multi-line mode, lookbehinds, POSIX classes, to name a few.

Example: how to use regular expressions to match strings

In the below dataset, suppose you want to identify the entries containing SKU codes.

Given that each SKU starts with 2 capital letters, followed by a hyphen, followed by 4 digits, you can match them using the following expression.

Pattern: \b[A-Z]{2}-\d{4}\b

Where [A-Z]{2} means any 2 uppercase letters from A to Z and \d{4} means any 4 digits from 0 to 9. A word boundary \b indicates that an SKU is a separate word and not part of a bigger string.

With the pattern established, start typing a formula like you normally do, and function's name will appear in the list suggested by Excel's AutoComplete:

Assuming the original string is in A5, the formula goes as follows:

=RegExpMatch(A5, "\b[A-Z]{2}-\d{3}\b")

For convenience, you can input the regular expression in a separate cell and use an absolute reference ($A$2) for the pattern argument. This ensures that the cell address will remain unchanged when you copy the formula to other cells:

=RegExpMatch(A5, $A$2)

To display your own text labels instead of TRUE and FALSE, nest RegExpMatch in the IF function and specify the desired texts in the value_if_true and value_if_false arguments:

=IF(RegExpMatch(A5, $A$2), "Yes", "No")

For more formula examples, please see:

• How to match strings using regular expressions
• Excel Data Validation with regexes

Example: how to extract strings using regular expressions

Taking our example a little further, let's extract invoice numbers. For this, we'll be using a very simple regex that matches any 7-digit number:

Pattern: \b\d{7}\b

Put the pattern in A2 and you'll get the job done with this compact and elegant formula:

=RegExpExtract(A5, $A$2)

If a pattern is matched, the formula extracts an invoice number, if no match is found - nothing is returned.

For more examples, please see: How to extract strings in Excel using regex.

Example: how to replace or remove strings using regexes

Some of our records contain credit card numbers. This information is confidential, and you may want to replace it with something or delete altogether. Both tasks can be accomplished with the help of the RegExpReplace function. How? In a second scenario, we'll be replacing with an empty string.

In our sample table, all card numbers have 16 digits, which are written in 4 groups separated with spaces. To find them, we replicate the pattern using this regular expression:

Pattern: \b\d{4} \d{4} \d{4} \d{4}\b

For replacement, the following string is used:

Replacement: XXXX XXXX XXXX XXXX

And here's a complete formula to replace credit card numbers with insensitive information:

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "XXXX XXXX XXXX XXXX")

With the regex and replacement text in separate cells (A2 and B2), the formula works equally well:

In Excel, "removing" is a particular case of "replacing". To remove credit card numbers, just use an empty string ("") for the replacement argument:

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "")

For more information, please see:

• How to replace strings in Excel using regex
• How to remove strings using regex
• How to strip off whitespace using regexes

How to use Regex in Excel

With the Ultimate Suite installed, using regular expressions in Excel is as simple as these two steps:

1. On the Ablebits Data tab, in the Text group, click Regex Tools.
   

2. On the Regex Tools pane, do the following:
   • Select the source data.
   • Enter your regex pattern.
   • Choose the desired option: Match, Extract, Remove or Replace.
   • To get the result as formula and not value, select the Insert as a formula check box.
   • Hit the action button.

   For instance, to remove credit card numbers from cells A2:A6, we configure these settings:
   

In a trice, an AblebitsRegex function will be inserted in a new column to the right of your original data. In our case, the formula is:

=AblebitsRegexRemove(A2, "\b\d{4} \d{4} \d{4} \d{4}\b")

Once the formula is there, you can edit, copy or move it like any native formula.

How to insert a Regex formula directly in a cell

The AblebitsRegex functions can also be inserted directly in a cell without using the add-in's interface. Here's how:

1. Click the fx button on the formula bar or Insert Function on the Formulas tab.
2. In the Insert Function dialog box, select the AblebitsUDFs category, choose the function of interest, and click OK.
   

3. Define the function's arguments like you normally do and click OK. Done!

For more information, please see Regex Tools for Excel.