Table of Contents
What is regex?
Regex, short for Regular Expressions, is a powerful tool for pattern matching in text data.
Plus, you might use the functions aboveinside other formulasto instantly upgrade their capabilities.
But first, let’s review how we got here.
A brief history of regex in Excel
Why doesnt Excel support Regex?
This is one of those questions that has bothered Excel power users for many years.
Its been a topic of heated debates and the cause of many clunky, complicated formulas.
for “one thing.”
They’re the flip phone of pattern matching.
Regex is a different story.
While wildcards are asking “Does this have an “a” followed by… stuff?
Want to match exactly three digits followed by optional whitespace and a hyphen?
Regex has patterns for that.
You get the idea.
Regex goesfar beyondbasic wildcards.
In the workbook below, the goal is to extract the numbers from the product codes in column B.
The problem is that the numbers vary in length, and their location in the product code also changes.
There’s just no easy way to figure out where each number begins and ends.
Instead, the formula below in cell D5 takes a “brute force” approach and simplyremovesall non-numeric characters.
It looks like this:
It’s not exactly obvious what this formula is doing, right?
you could find anexplanation here.
Does it help in this case?Yes.
Regex helps a lot!
In the worksheet below, the new formula in cell D5 is based on the REGEXEXTRACT function.
Here it is:
Yep, that’s the whole formula.
Basically, we are asking REGEXEXTRACT for a sequence of 1 or more numbers.
you might see the results below.
The REGEXTEST function
The Excel REGEXTEST function tests for a given regex pattern.
The result from REGEXTEST is TRUE or FALSE.
REGEXTEST opens up new possibilities for data validation and text analysis directly within Excel formulas.
The ‘\b’ is a word boundary character.
It will match a space and any punctuation that typically appears around a word.
The ‘|’ creates OR logic.
Learn more about the REGEXTEST function.
The REGEXEXTRACT function
The REGEXEXTRACT function extracts specific information from a string based on a Regex pattern.
Its perfect for pulling out key pieces of data from messy text.
This formula is looking for and extracting phone numbers that follow a pattern like this: “123-456-7890”.
Learn more about the REGEXEXTRACT function.
Its very useful for cleaning up or reformatting text.
you might think of REGEXREPLACE as a much more powerful version of the simplisticSUBSTITUTE function.
Learn more about the REGEXREPLACE function.
Regex quick reference
Regex relies onpatternsto match specific text.
The table below contains some simple regex patterns.
These patterns can be combined to create verycapabletext-matching formulas.
Matching substring vs. entire text
By default, regex will match anysubstringsthat match the pattern.
Regex tips
Regex patterns can get complicated fast.
As you might see in the table above, regex has a learning curve.
Because regex is like a mini-language, the large number of symbols and patterns can be intimidating.
However, you don’t need tomasterregex in order touseregex.
A little goes a long way.
The good news is there has never been a better time to learn to use regex.
There are many great resources on the web to help you create the patterns you need.
Here are a few of my favorites:
