Now that Excel is getting proper Regular Expressions (Regex) here’s some resources to get you started.
The upcoming Excel functions RegexTest(), RegexExtract() and RegexReplace() use a common type of regex (PCRE2) rather than some special Microsoft version. That’s important because each regex version has its own options and peculiarities. Using a common type of Regular Expressions means there are many books and online resources that will directly and specifically apply to the new Excel functions.
Compare that to Excel VBA has had regular expressions for many years and Word’s Advanced Find has a form of regex. Both use some Microsoft version of regex which isn’t fully documented and relies on a lot of ‘trial and error’ experimentation by paying customers.
What are Regular Expressions?
Regular Expressions are a way to find and change text strings in very powerful and complex ways.
You’ve probably used some Regex options already because they are part of the common search terms used in all software:
* or wildcard to denote any string of text e.g. *.docx to find all Word documents or b*t to find any word starting with b and ending in t like bat, boot, blunt or biotechnologist.
? single character wildcard e.g. b?t will find bat, bot, bit, but and even b5t but not boot.
However Regular Expressions can do a lot more, including specifying what characters to find. For example:
b[a-z]t will find a word starting with ‘b’ and ending with ‘t’ with only one lower-case letter in between. It’ll find bat, bot, bit, but. NOT bAt or b5t
b[a-z]*t adding a * after the [pattern] means any number of characters to match. It’ll find bot, boot, blunt. NOT b123t
What can Regex do?
Not only can regex find complex strings but it can also parse them into segments to rearrange, remove or add to. The regular expression can contain (brackets) to break the string into groups, then replaced in a different order using $1 $2 etc.
Here’s a VERY simple example using () to separate the first and second words then reverse them.
"([A-z]+) ([A-z]+)" "$2 , $1"
Use that to switch first and last name with a comma in between.
Regular Expressions are VERY powerful and often you’ll see many different solutions to the same problem. That’s partly due to the level of complexity you need, assumptions made about the input string, the type of regex being used and possibly the relative positions of the sun and moon.
More than one answer
To rewrite an old joke …
“Put two Regex experts in a room and you’ll get FOUR opinions”.
Any regex expert would be horrified by our ‘first and last name’ example above because it’s overly simplistic.
A common regex question is how to verify the format of an email address to make sure there’s text, then an @ followed by a valid domain name. Seems simple but can get complicated, check out this StackOverflow page just to begin. Or here’s a recommended email address tester, said to be compliant with the official Internet standard (RFC2822).
Regex online testing
“Try it and see” is the best way to learn regular expressions.
There are many online sites for regular expressions that let you test regex and also understand how it works (or not).
Regex101.com is one of the best-known sites with many useful features, especially for newcomers.
Choose a regex ‘flavor’ on the left. For the Excel functions choose ‘PCRE2’.
Enter a regex pattern then a text string to see if and how it matches.
Hover the mouse pointer over the color-coded text to see a tooltip explanation.
On the right is a breakdown of how the current regex pattern works.
Match information shows the matches and capturing groups found.
Tip: sign-in to Regex101.com (via Google or Github) to save your regex tests between sessions.
Community Patterns
Part of Regex101.com is Community Patterns, a collection of suggested regex patterns submitted and voted upon. Search for a pattern and perhaps filter by regex flavor. Look for patterns that have many votes.
Multiple String Testing
One thing missing from Regex101.com is multiple string testing. Usually, testing regular expressions means having a list of strings to cover many examples that should or should not match. Other sites like https://regexr.com have more than one test available, here you can see that a simple name pattern works OK but the pattern fails for a more complicated name with a middle initial.
AI suggestions
Another place for regex patterns is any AI system, ChatGPT, Copilot, Gemini etc.
Here’s what Copilot offered for one regex question. The response was in Python code, even though that wasn’t asked for, however the regex pattern is there.
AI results aren’t always reliable, see Easily learn and make Office VBA with AI
Online Tutorials
Microsoft has a Regular Expressions Quick Reference that’s as good a place to start as any. The whole thing can be downloaded as a Word document or PDF.
Keep in mind that the Microsoft guide is for .NET programmers, not specifically the PCRE2 flavor used by the Excel functions. It should serve for the regex basics.
