These 3 new Excel functions ended a decade of my complicated text-cleaning formulas

A vendor export landed in my inbox with phone numbers in four different formats, customer names stored last-first, and order IDs, where five of twelve rows broke the house format. I'd already swapped out the older Excel functions that still turn up in most workbooks, and this is the same move one layer down.

REGEXTEST, REGEXEXTRACT, and REGEXREPLACE let you describe what the text looks like rather than tell Excel where it sits. That change retired formulas I'd been rewriting for years, and these five jobs are where it showed first.

These three functions run in Excel for Microsoft 365 and Excel for the web. Perpetual versions like Excel 2021 and Excel 2024 don't include them, so check your build first.

It catches the broken order IDs before they reach a report

Screenshot by Yasir Mahmood

Screenshot by Yasir Mahmood

Screenshot by Yasir Mahmood

Screenshot by Yasir Mahmood

Close Screenshot by Yasir Mahmood

Screenshot by Yasir Mahmood

Screenshot by Yasir Mahmood

Screenshot by Yasir Mahmood

Our order IDs are supposed to be three uppercase letters, a hyphen, then four digits. Nothing in the export enforces that, so I check the column before anything downstream touches it.

All three of these functions take something called a regular expression, or regex. That's a short description of what a piece of text looks like, written in a compact shorthand. Rather than telling Excel to look at the fourth character, you describe the whole shape and let Excel find what matches.

REGEXTEST is the simplest of the three. It reads a cell, compares it against your description, and returns TRUE or FALSE. Here's the syntax.

=REGEXTEST(text, pattern, [case_sensitivity])

text is the cell or range you want to check.

is the cell or range you want to check. pattern is the regular expression describing a valid entry.

is the regular expression describing a valid entry. case_sensitivity is optional and defaults to 0, which is case-sensitive. Set it to 1 to ignore case.

Here's what that description looks like for our order IDs.

=REGEXTEST(A2, "^[A-Z]{3}-[0-9]{4}$")

That string in quotes reads left to right, one piece at a time. The ^ and $ pin the match to the start and end of the string, so ELEC-7724 fails on its four-letter prefix rather than passing on a partial match. [A-Z]{3} means three uppercase letters, and [0-9]{4} means four digits.

The formula this replaced needed four checks to say the same thing.

=AND(LEN(A2)=8, MID(A2,4,1)="-", ISNUMBER(VALUE(RIGHT(A2,4))), EXACT(LEFT(A2,3), UPPER(LEFT(A2,3))))

That version holds until a supplier ships a five-letter prefix, and then every check behind it is wrong.

Related This Excel function eliminated hours of manual text combining Sifting through data just got easier with this quick and easy Excel formula.

REGEXEXTRACT pulls values without counting characters

The prefix length changes and the answer still comes back right

Screenshot by Yasir Mahmood

Flagging a bad row is one job. Pulling a usable value out of a good one is the next, and it's where counting characters really falls apart.

Product codes in the same export run from ELEC-NORTH-4471-A to HOMEGD-EAST-3310-A . I need the four-digit order number out of the middle, but the prefix is four characters on some rows and six on others, so it never sits in the same place twice.

REGEXEXTRACT works like REGEXTEST, except it hands back the matching text instead of TRUE or FALSE. It has the following syntax:

=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

text is the string you want to pull from.

is the string you want to pull from. pattern describes the piece you want back.

describes the piece you want back. return_mode is optional. Use 0 for the first match, 1 for all matches, and 2 for the capture groups inside the first match.

is optional. Use 0 for the first match, 1 for all matches, and 2 for the capture groups inside the first match. case_sensitivity behaves the same as it does in REGEXTEST.

Since I already know how to describe four digits from the pattern above, the formula is short.

=REGEXEXTRACT(D2, "[0-9]{4}")

No anchors this time, because I want a match from anywhere in the string rather than a whole-cell match. Excel scans across and finds the first run of four consecutive digits, returning it; therefore, the prefix length no longer matters.

MID would need a different starting position for each code shape, and when it guesses wrong, it hands back the wrong four characters rather than an error, which is harder to spot. One thing to watch is that all three functions return text, so wrap the result in VALUE when you need a number you can add up.

That said, regex isn't always the shortest road. When the delimiter is genuinely consistent, the functions that split and extract text by delimiter instead of by position are still the shorter formula.

Related Excel's best cleanup trick is hidden behind Ctrl+G The Special button next to OK is the part of Go To most people never open.

REGEXREPLACE retired my nested formulas

Five layers of nesting collapse into a single line

Screenshot by Yasir Mahmood

Testing and extracting both leave the original cell alone. Rewriting it is the third job, and the one that saved me the most typing.

Phone numbers in the export come with parentheses, dots, spaces, country codes, and sometimes nothing at all. I want digits and nothing else. REGEXREPLACE has the following syntax:

=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

text is the string being cleaned.

is the string being cleaned. pattern describes what to find.

describes what to find. replacement is what goes in its place. Leave it as an empty pair of quotes to delete the match outright.

is what goes in its place. Leave it as an empty pair of quotes to delete the match outright. occurrence is optional and replaces every match by default. A positive number targets one match instead.

is optional and replaces every match by default. A positive number targets one match instead. case_sensitivity behaves the same as it does in the other two.

Stripping a phone column down to digits takes one pattern.

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

Watch the caret here, because it's doing the opposite of what it did earlier. Outside square brackets, ^ anchors to the start of the string. Inside them, it flips the list around, so [^0-9] means any character that is not a digit. Every one of those is replaced with nothing, leaving ten clean digits behind.

This is what I used to write for the same result, and each layer removes exactly one character.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")",""),"-","")," ",""),".","")

Screenshot by Yasir Mahmood

Reversing names stored last-first is the more interesting case because it requires keeping both halves of the name and swapping them rather than deleting them.

=REGEXREPLACE(B2, "(.+),\s+(.+)", "$2 $1")

Excel refers back to captured groups with $1 and $2 , not the \1 and \2 most regex references use. The \s+ matters too. With a plain space in the pattern, a row carrying a double space after the comma returns a leading space, and you won't see it until you click the cell.

Quiz 5 Questions Regex Moves That Save a Spreadsheet Your Top Score -- Attempts -- Average 96% Users 16 Start Quiz 0 0

The three functions work best together

One pass over the export instead of one formula per column

Screenshot by Yasir Mahmood

Used one at a time, these are three handy formulas. Used together, they turn an afternoon of column-by-column cleanup into a single pass.

REGEXTEST accepts a whole range rather than a single cell and automatically spills its results down the sheet. That means one formula can drive an entire validation column without being filled down. Wrapping it in FILTER goes further, pulling out only the rows that failed, so you never have to scroll looking for them.

=FILTER(A2:A13, NOT(REGEXTEST(A2:A13, "^[A-Z]{3}-[0-9]{4}$")))

NOT flips each TRUE to FALSE, so FILTER keeps the rows that failed the check rather than those that passed. I've written about how much one FILTER formula handles on its own, and this is the version I reach for most.

The notes column gets the same treatment. TRIM clears leading and trailing spaces but leaves double spaces sitting in the middle of a string, so I run both.

=REGEXREPLACE(TRIM(G2), "\s{2,}", " ")

Screenshot by Yasir Mahmood

Two or more whitespace characters in a row become a single space. The braces work exactly as they did in the very first pattern, except the comma means two or more rather than an exact count.

Regex patterns are greedy by default, so a pattern that looks right can return more than you expected. Test on five rows before filling down a column of 500.

Regex is not right for every cleanup job

Readability and sharing are the trade-offs

Screenshot by Yasir Mahmood

None of this makes regex the answer to everything, and two limits are worth knowing before you rewrite half your workbook.

The first is readability. A pattern you wrote in March is unreadable by June unless you leave a note beside it explaining what it targets. I keep a short line in an adjacent cell for anything beyond a plain digit strip.

Sharing is the bigger catch. Anyone opening your workbook on a perpetual license sees #NAME? in every regex cell, so the file reads as broken rather than unsupported. Worth a heads-up before you send it.

There's also a question of scale. For an import that arrives the same way every month, cleaning a messy imported spreadsheet with Power Query is still the better answer because a query re-runs on refresh, whereas a worksheet formula does not. Regex wins the middle ground instead, the one-off cleanup that's too varied for Flash Fill and too small to justify building a query.

Where I'm pointing these next

Excel now accepts regex patterns inside XLOOKUP and XMATCH through match mode 3, so the same pattern that flags a bad column can also find a row. That puts regex right alongside the lookup formulas that cost the most time.

The pairing I want next is REGEXEXTRACT feeding TEXTSPLIT, so a messy string gets cleaned and broken into columns in one step. Describing the shape of your text rather than its position is the habit worth keeping, and the old formulas don't come back once you have it.