Regular Expressions in Excel

After decades of waiting, Excel finally supports Regular Expressions, aka regex! Learn how three powerful new functions - REGEXTEST, REGEXREPLACE, and REGEXEXTRACT - can transform complex text operations into elegant, maintainable formulas. Whether you're cleaning data, validating inputs, or extracting patterns, these game-changing tools will change how you write formulas in Excel.

What is regex? Regex, short for Regular Expressions, is a powerful tool for pattern matching in text data. Using a combination of metacharacters, literal characters, character classes, and quantifiers, you can define complex search patterns to extract, validate, or manipulate text data. The main benefit of regex in Excel is the ability to work with text very precisely without resorting to complicated formulas that are hard to understand and maintain.

Why doesn’t Excel support Regex?

This is one of those questions that has bothered Excel power users for many years. It’s been a topic of heated debates and the cause of many clunky, complicated formulas. Well, the wait is over! In December 2024, Microsoft finally added regex support to Excel formulas, bringing powerful text-processing tools directly to your favorite spreadsheet software. Regex support comes primarily from the introduction of three brand-new functions:

The REGEXTEST function
The REGEXREPLACE function
The REGEXEXTRACT function

In addition to the three dedicated functions above, XLOOKUP and XMATCH have also been upgraded to support regex as one of their match modes. Plus, you can use the functions above inside another function to instantly upgrade capabilities. For example, you can use REGEXTEST inside IF as the logical test. In this article, I want to introduce these new functions and give you some sense of how we got here and what it means going forward.

Note: These new regex functions are only available in Excel 365 for now.

A brief history of regex in Excel

Although regex is a standard feature in many programming languages, it was notably absent from Excel for most of its history. Here's how Excel's text processing capabilities evolved:

Excel has always supported basic wildcards (* and ?), but these are primitive compared to regex patterns. Users had to rely on combining functions like LEFT, RIGHT, MID, FIND, SEARCH, and SUBSTITUTE for pattern matching, resulting in complex, hard-to-maintain formulas.
Power users worked around these limitations using VBA, Power Query, or custom add-ins for regex support, but these tools require different skills and are not available to all users.
In Excel 2013, Microsoft added some regex-like capability with the FILTERXML function, which uses XPath queries for pattern matching. Still, this function is not widely used or available in Excel for Mac.
In 2022, Microsoft improved text handling with TEXTSPLIT, TEXTBEFORE, and TEXTAFTER functions. These functions make it much easier to split text at specific locations in an Excel formula. However, they do not support regex.
In December 2024, Excel introduced three regex functions: REGEXTEST, REGEXREPLACE, and REGEXEXTRACT. These functions modernize Excel's text-processing capabilities and bring it up to speed with other professional tools.

Now that Excel supports regex directly, many complicated formulas of the past can be drastically simplified.

Regex vs. Excel Wildcards

Excel wildcards are like a toolbox with just two tools: * for "anything" and ? for "one thing." Sure, you can find "apple*" or "?at," but that's about it. 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?", regex is performing complex queries like "Any word that starts with a capital letter, contains exactly two numbers, and ends with x or y but not z".

Want to match exactly three digits followed by optional whitespace and a hyphen? Try \d{3}\s*- Need to find an email address or validate a strong password? Regex has patterns for that. It can even "look ahead" in your text to match patterns only when they occur before something else.

You get the idea. Regex goes far beyond basic wildcards.

How regex is useful

Before we get into the details, let's look at a specific example of how regex can help simplify a formula. In the workbook below, the goal is to extract the numbers from the product codes in column B. With hundreds of functions available, you might think this is a simple problem in Excel, but it's not! 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 simply removes all non-numeric characters. It looks like this:

=TEXTJOIN("",TRUE,IFERROR(MID(B5,SEQUENCE(LEN(B5)),1)+0,""))

Example of a complicated formula before regex

It's not exactly obvious what this formula is doing, right? You can find an explanation here. It's pretty complicated, and I'm even cheating a bit because I'm assuming at least Excel 2021, which has the SEQUENCE function. In Excel 2019, things get uglier because we need to spin up our own number array with the volatile INDIRECT function and the ROW function:

=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)+0,""))

In Excel versions before 2019, the formula becomes even more complicated!

What about regex? 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:

=REGEXEXTRACT(B5,"\d+")

Yep, that's the whole formula. Basically, we are asking REGEXEXTRACT for a sequence of 1 or more numbers. You can see the results below. I think you'll agree that this new formula is a lot simpler 🙂

Example of a simple formula after regex

Now that you have a taste of how regex can help simplify difficult formulas let's look more closely at the three new regex functions.

The REGEXTEST function

The Excel REGEXTEST function tests for a given regex pattern. The result from REGEXTEST is TRUE or FALSE. For example, =REGEXTEST(A1,"[0-9]") will return TRUE if cell A1 contains any numeric digit, and =REGEXTEST(A1,"[A-Z]" will return TRUE if A1 contains any uppercase letters. REGEXTEST opens up new possibilities for data validation and text analysis directly within Excel formulas. In the worksheet below, REGEXTEST is configured to test addresses for one of four US states: MN, MT, ND, and SD. The formula in D5, copied down, looks like this:

=REGEXTEST(B5,"\b(MN|MT|ND|SD)\b")

The pattern "\b(MN|MT|ND|SD)\b" matches MN, MT, ND, or SD. 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.

Example the REGEXTEST function with or logic

Note: While you probably don't need a TRUE or FALSE result in a case like this, you could use exactly the same formula above inside the FILTER function to list all addresses that contain MN, MT, ND, or SD.

Learn more about the REGEXTEST function.

The REGEXEXTRACT function

The REGEXEXTRACT function extracts specific information from a string based on a Regex pattern. It’s perfect for pulling out key pieces of data from messy text. For example, in the worksheet below, the goal is to extract telephone numbers in the format xxx-xxx-xxxx from the text strings in column B. The formula in cell D5, copied down, looks like this:

=REGEXEXTRACT(B5,"\d{3}-\d{3}-\d{4}")

Example the REGEXEXTRACT function with telephone numbers

You can get a sense of the power and flexibility of regex with this example. This formula is looking for and extracting phone numbers that follow a pattern like this: "123-456-7890". Let's break down each part:

'\d{3}' looks for exactly 3 digits
'-' looks for a hyphen
'\d{3}' looks for 3 more digits
'-' looks for another hyphen
'\d{4}' looks for 4 more digits

So, if you have text that contains something like "My phone number is 555-123-4567", this formula will pull out just "555-123-4567".

Learn more about the REGEXEXTRACT function.

The REGEXREPLACE function

The REGEXREPLACE function allows you to replace parts of a string that match a Regex pattern with something else. It’s very useful for cleaning up or reformatting text. You can think of REGEXREPLACE as a much more powerful version of the simplistic SUBSTITUTE function. For example, in the workbook below, REGEXREPLACE is configured to remove all non-numeric characters from the telephone numbers in column B. The formula in cell D5, copied down, is:

=REGEXREPLACE(B5,"[^0-9]","")

Example the REGEREPLACE function to clean telephone numbers

The pattern "[^0-9]" will match any character that is not a number. Since the replacement pattern is an empty string (""), the result is that REGEXREPLACE effectively "strips" all non-numeric characters from the input, and the final result contains numbers only.

Learn more about the REGEXREPLACE function.

Regex cheatsheet

Regex relies on patterns to match specific text. The table below contains some simple regex patterns. These patterns can be combined to create very capable text-matching formulas.

Pattern	Description and examples
abc	Matches the literal text 'abc'. Example: 'abc' matches 'abc', but not 'ABC' or 'ab'.
.	Matches any single character except a newline. Example: 'c.t' matches 'cat', 'cot', 'c@t'.
\d	Matches any digit (0-9). Example: '\d\d\d' matches '123', '999'.
\w	Matches any word character (letter, digit, underscore). Example: '\w\w' matches 'ab', '1_', 'A9'.
\s	Matches any whitespace character (space, tab, newline). Example: 'a\sb' matches 'a b'.
\b	Matches a word boundary. Example: '\bcat\b' matches 'cat' in 'the cat sits' but not 'category'.
[abc]	Matches any one character listed in brackets. Example: 'gr[ae]y' matches 'gray' and 'grey'.
[a-z]	Matches any one character in the range. Example: '[a-z]' matches any lowercase letter.
[^abc]	Matches any character NOT listed. Example: '[^0-9]' matches any non-digit.
a*	Matches 0 or more 'a'. Example: 'ca*t' matches 'ct', 'cat', 'caaat'.
a+	Matches 1 or more 'a'. Example: 'ca+t' matches 'cat', 'caat', but not 'ct'.
a?	Matches 0 or 1 'a'. Example: 'colou?r' matches 'color' and 'colour'.
a{3}	Matches exactly 3 'a'. Example: 'a{3}' matches 'aaa', but not 'aa' or 'aaaa'.
a{2,4}	Matches 2 to 4 'a'. Example: 'a{2,4}' matches 'aa', 'aaa', 'aaaa'.
^abc	Matches 'abc' at start of string. Example: '^The' matches 'The cat' but not 'In The'.
abc$	Matches 'abc' at end of string. Example: '.com$' matches 'example.com'.
(abc)	Groups pattern and captures match. Example: '(cat\|dog)s' matches 'cats' and 'dogs'.
(?:abc)	Non-capturing group. Like () but doesn't store the match.
a\|b	Matches 'a' or 'b'. Example: 'cat\|dog' matches 'cat' or 'dog'.

Basic regex tips

Regex patterns can get complicated fast. Here are some general tips for creating and debugging regex patterns:

Start small - Test the simplest version of your pattern first, then add complexity. If '\d+' doesn't work, '\d{3}-\d{3}' probably won't either.
Use REGEXTEST to validate your patterns against sample data. REGEXTEST returns TRUE or FALSE, so it is perfect for testing in Excel.
Special characters (. * + ? etc.) need to be escaped with '\' to match these characters literally.
Regular expressions are case-sensitive by default. You can disable case_sensitivity to 1 for a case-insensitive match for each of the regex functions in Excel.
^ and $ match start/end of the entire string, not individual lines
If a pattern isn't matching, try making it more permissive. For example, \s* instead of just a space to handle variable whitespace.

Summary

The formal introduction of regex in Excel formulas is a game-changer. Many complicated formulas of the past will slowly disappear as people learn how to turn "spaghetti code" into elegant and reliable formulas based on regex. As you can 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 to master regex in order to use regex. 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: