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

A vendor export landed in my inbox with cellphone numbers in 4 completely different codecs, buyer names saved last-first, and order IDs, the place 5 of twelve rows broke the home format. I would already swapped out the older Excel functions that still turn up in most workbooks, and this is similar transfer one layer down.

REGEXTEST, REGEXEXTRACT, and REGEXREPLACE allow you to describe what the textual content seems like reasonably than inform Excel the place it sits. That change retired formulation I would been rewriting for years, and these 5 jobs are the place it confirmed first.

REGEXTEST validates a column in a single system

It catches the damaged order IDs earlier than they attain a report

Our order IDs are presupposed to be three uppercase letters, a hyphen, then 4 digits. Nothing within the export enforces that, so I test the column earlier than something downstream touches it.

All three of those capabilities take one thing known as an everyday expression, or regex. That is a brief description of what a bit of textual content seems like, written in a compact shorthand. Slightly than telling Excel to have a look at the fourth character, you describe the entire form and let Excel discover what matches.

REGEXTEST is the best of the three. It reads a cell, compares it towards your description, and returns TRUE or FALSE. Here is the syntax.

=REGEXTEST(textual content, sample, [case_sensitivity])

• textual content is the cell or vary you wish to test.
• sample is the common expression describing a sound entry.
• case_sensitivity is elective and defaults to 0, which is case-sensitive. Set it to 1 to disregard case.

Here is what that description seems like for our order IDs.

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

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

The system this changed wanted 4 checks to say the identical factor.

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

That model holds till a provider ships a five-letter prefix, after which each test behind it’s incorrect.

The prefix size modifications and the reply nonetheless comes again proper

Flagging a nasty row is one job. Pulling a usable worth out of a very good one is the subsequent, and it is the place counting characters actually falls aside.

Product codes in the identical export run from ELEC-NORTH-4471-A to HOMEGD-EAST-3310-A. I would like the four-digit order quantity out of the center, however the prefix is 4 characters on some rows and 6 on others, so it by no means sits in the identical place twice.

REGEXEXTRACT works like REGEXTEST, besides it palms again the matching textual content as an alternative of TRUE or FALSE. It has the next syntax:

=REGEXEXTRACT(textual content, sample, [return_mode], [case_sensitivity])

• textual content is the string you wish to pull from.
• sample describes the piece you need again.
• return_mode is elective. Use 0 for the primary match, 1 for all matches, and a pair of for the seize teams inside the primary match.
• case_sensitivity behaves the identical because it does in REGEXTEST.

Since I already know how you can describe 4 digits from the sample above, the system is brief.

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

No anchors this time, as a result of I need a match from anyplace within the string reasonably than a whole-cell match. Excel scans throughout and finds the primary run of 4 consecutive digits, returning it; due to this fact, the prefix size now not issues.

MID would want a special beginning place for every code form, and when it guesses incorrect, it palms again the incorrect 4 characters reasonably than an error, which is more durable to identify. One factor to observe is that every one three capabilities return textual content, so wrap the lead to VALUE if you want a quantity you may add up.

That stated, regex is not at all times the shortest highway. When the delimiter is genuinely constant, the capabilities that split and extract text by delimiter instead of by position are nonetheless the shorter system.

REGEXREPLACE retired my nested formulation

5 layers of nesting collapse right into a single line

Testing and extracting each depart the unique cell alone. Rewriting it’s the third job, and the one which saved me probably the most typing.

Telephone numbers within the export include parentheses, dots, areas, nation codes, and generally nothing in any respect. I would like digits and nothing else. REGEXREPLACE has the next syntax:

=REGEXREPLACE(textual content, sample, alternative, [occurrence], [case_sensitivity])

• textual content is the string being cleaned.
• sample describes what to search out.
• alternative is what goes as a replacement. Depart it as an empty pair of quotes to delete the match outright.
• prevalence is elective and replaces each match by default. A constructive quantity targets one match as an alternative.
• case_sensitivity behaves the identical because it does within the different two.

Stripping a cellphone column all the way down to digits takes one sample.

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

Watch the caret right here, as a result of it is doing the alternative of what it did earlier. Outdoors sq. brackets, ^ anchors to the beginning of the string. Inside them, it flips the record round, so [^0-9] means any character that’s not a digit. Each a type of is changed with nothing, leaving ten clear digits behind.

That is what I used to write down for a similar outcome, and every layer removes precisely one character.

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

Reversing names saved last-first is the extra attention-grabbing case as a result of it requires preserving each halves of the title and swapping them reasonably than deleting them.

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

Excel refers again to captured teams with $1 and $2, not the 1 and 2 most regex references use. The s+ issues too. With a plain area within the sample, a row carrying a double area after the comma returns a number one area, and you will not see it till you click on the cell.

The three capabilities work finest collectively

One go over the export as an alternative of 1 system per column

Used one by one, these are three useful formulation. Used collectively, they flip a day of column-by-column cleanup right into a single go.

REGEXTEST accepts a complete vary reasonably than a single cell and robotically spills its outcomes down the sheet. Which means one system can drive a whole validation column with out being stuffed down. Wrapping it in FILTER goes additional, pulling out solely the rows that failed, so that you by no means must scroll on the lookout for them.

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

NOT flips every TRUE to FALSE, so FILTER retains the rows that failed the test reasonably than people who handed. I’ve written about how much one FILTER formula handles on its own, and that is the model I attain for many.

The notes column will get the identical therapy. TRIM clears main and trailing areas however leaves double areas sitting in the course of a string, so I run each.

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

Two or extra whitespace characters in a row grow to be a single area. The braces work precisely as they did within the very first sample, besides the comma means two or extra reasonably than a precise rely.

Regex just isn’t proper for each cleanup job

Readability and sharing are the trade-offs

None of this makes regex the reply to every part, and two limits are price figuring out earlier than you rewrite half your workbook.

The primary is readability. A sample you wrote in March is unreadable by June until you permit a observe beside it explaining what it targets. I maintain a brief line in an adjoining cell for something past a plain digit strip.

Sharing is the larger catch. Anybody opening your workbook on a perpetual license sees #NAME? in each regex cell, so the file reads as damaged reasonably than unsupported. Value a heads-up earlier than you ship it.

There’s additionally a query of scale. For an import that arrives the identical method each month, cleaning a messy imported spreadsheet with Power Query remains to be the higher reply as a result of a question re-runs on refresh, whereas a worksheet system doesn’t. Regex wins the center floor as an alternative, the one-off cleanup that is too various for Flash Fill and too small to justify constructing a question.

The place I am pointing these subsequent

Excel now accepts regex patterns inside XLOOKUP and XMATCH by match mode 3, so the identical sample that flags a nasty column also can discover a row. That places regex proper alongside the lookup formulas that cost the most time.

The pairing I would like subsequent is REGEXEXTRACT feeding TEXTSPLIT, so a messy string will get cleaned and damaged into columns in a single step. Describing the form of your textual content reasonably than its place is the behavior price preserving, and the previous formulation do not come again after you have it.

OS

Home windows, macOS

Supported Desktop Browsers

All through internet app

Developer(s)

Microsoft

Free trial

One month

Worth mannequin

Subscription

iOS suitable

Sure