Imagine you need to extract a simple number from a complex string, such as a product code with region-specific abbreviations or a column of phone numbers stored in four different formats. To make matters worse, because the data came from an external source, it might also contain trailing spaces, inconsistent formatting, or other small errors.
To handle these problems, you could build a formula using MID and FIND, rely on Flash Fill to guess what you want, use Excel’s Find and Replace tool, or bring out the heavy hitters like Power Query or VBA UDFs (user-defined functions). While all of these approaches can get the job done, I’ve found that none of them are as intuitive for text parsing and data cleanup as Excel’s new native regex functions.
These simple Power Query commands save me hours of data cleanup in Excel
Cleaning up data shouldn’t be a chore with Power Query at your disposal!
The three regex functions you need to know
REGEXTEST, REGEXEXTRACT, and REGEXREPLACE make cleaning and extracting text much less painful
Microsoft introduced three regex functions for Excel (REGEXTEST, REGEXEXTRACT, and REGEXREPLACE) to Microsoft 365 Insider builds in mid-2024, with a broader rollout following through 2025. Availability still depends on your Excel for Microsoft 365 subscription, build version, and platform, so if you don’t see them yet, it’s worth checking whether your channel has received the update.
Basically, the idea behind regex is that instead of telling Excel where something is, you describe what it looks like. Each of these three functions approaches that task differently.
REGEXTEST
REGEXTEST is the simplest of the three. It takes a cell and a pattern and returns TRUE or FALSE depending on whether the text matches. If you have a column of order IDs and want to flag any row that does not contain exactly three uppercase letters followed by four digits, you can use this:
=REGEXTEST(G2:G100, "^[A-Z]{3}[0-9]{4}$")
After the text argument, which specifies the text or cell reference you want to check, the second argument is the regex pattern that describes what Excel should look for. In this example, the ^ character anchors the match to the start of the string, [A-Z] matches any uppercase letter from A to Z, and {3} specifies that the letter pattern must appear three times. The [0-9] portion matches any digit, {4} requires four consecutive digits, and the $ character anchors the match to the end of the string.
Because I did not specify the optional case-sensitivity argument, the function uses its default value of 0 (case-sensitive). As a result, the formula returns FALSE if the letters are lowercase.
Any cell that deviates from the specified format returns FALSE. For example, ABC1234 returns TRUE, while ABCD12345 returns FALSE. You can then sort or filter the results, or use them to drive conditional formatting rules in Excel.
REGEXEXTRACT takes things a step further by pulling out text that matches a pattern. Using the same example of three letters followed by four digits, you can extract just the digits with either of these formulas:
=REGEXEXTRACT(G2:G100, "\d{4}")
=REGEXEXTRACT(G2:G100, "[0-9]{4}")
Here, \d matches any digit from 0 to 9, while {4} specifies that four consecutive digits must be present for a match.
In addition to the arguments used by REGEXTEST, REGEXEXTRACT includes a return_mode argument before the case-sensitivity argument. By default, the function returns only the first match it finds. However, using values such as 1 or 2 allows you to return all matches or specific capture groups, which is useful when a single cell contains multiple values that need to be separated.
If your text includes a hyphen between the letters and numbers, you could use this instead:
=REGEXEXTRACT(G2:G100, "(?
The parentheses create a capture group, allowing you to return only the portion of the match you need.
These regex functions always return text. If you need a numeric value, wrap the formula in the VALUE function.
REGEXREPLACE
REGEXREPLACE finds matching text and replaces it with whatever you specify. Here’s its syntax:
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
For example, if you want to strip everything except digits from a column of phone numbers, you can use this:
=REGEXREPLACE(D2:D12, "[^0-9]", "")
This formula replaces every character that is not a digit with nothing, leaving only the numbers, regardless of whether the original format used dashes, dots, spaces, or parentheses. You can also use REGEXREPLACE to reorganize text. For example, it can transform names stored as “LastName, FirstName” into “FirstName LastName” using capture groups and replacement patterns. Once you understand the basics, the range of possible transformations becomes broad.
All three functions use the PCRE2 regex engine, which is widely supported across other tools and programming languages. If you already use regex in JavaScript, Python, or Google Sheets, many of the patterns you know will transfer with little or no modification. Even if you’re new to PCRE2, you can use any of your favorite free AI chatbots to generate regex patterns for the specific text-cleaning tasks you need to perform.
Excel’s regex in the real world
It can solve a lot of problems, but it isn’t always the answer
Whenever you need to find text that follows a particular pattern in Excel, there are few tools better suited to the job than regex. And if you regularly work with exports from CRMs, ERPs, survey platforms, inventory systems with inconsistent SKU formats, or customer databases where phone numbers and email addresses end up mixed together, you’ll find plenty of opportunities to use it.
Validation is another area where Excel’s regex functions shine. Running REGEXTEST down a column before importing data into another system allows you to catch formatting issues in the spreadsheet rather than discovering them later in an error log. Whether you’re flagging email addresses missing an @ symbol, date fields containing letters, or ZIP codes the wrong length, each check can be handled with a single formula.
That said, regex is not perfect. The patterns can be difficult to read and write, and your formulas can become cryptic if they aren’t documented. For that reason, it’s worth leaving notes about what you’re trying to achieve as you build them, whether you’re revisiting the spreadsheet months later or sharing it with someone else.
It’s also important to recognize where regex stops being the best tool for the job. For large-scale or repeatable data transformation work, such as multi-table reconciliations, automated monthly imports, or complex data pipelines, Power Query, Python, or a dedicated ETL (Extract, Transform, Load) process is usually the more efficient solution. Regex formulas inside worksheet cells do not scale in the same way, nor were they designed to replace those tools.
Where regex excels is in the middle ground: cleanup tasks that are too varied for Flash Fill or too small to justify building a full Power Query workflow, but structured enough that a well-written pattern can handle every row consistently.
Less time fixing data, more time using it
With Excel’s three regex functions, I’ve gone from relying on complex legacy workarounds and formulas that depend on where text sits in a cell to simply describing what I’m looking for. I still work with the same inconsistent exports and messy imports, but I now spend far less time wrestling with data validation and transformation before I can actually use the information.
If you’d like to retire some of your more complicated formulas and workarounds in favor of a single formula that can be applied across a range of cells, Excel’s regex functions are well worth learning. They won’t replace every data-cleaning tool in your workflow, but for many everyday cleanup and extraction tasks, they’re often the fastest and most straightforward solution.
- OS
-
Windows, macOS
- Supported Desktop Browsers
-
All via web app
- Developer(s)
-
Microsoft
- Free trial
-
One month
- Price model
-
Subscription
- iOS compatible
-
Yes
Microsoft Excel is a powerful spreadsheet application used for data organization, analysis, and visualization. It supports formulas, functions, pivot tables, and charts to process complex datasets efficiently. Widely used in business and education, Excel also integrates with other Microsoft 365 apps for collaboration, automation, and real-time data insights.
