Regular Expressions in Excel!

Regular Expressions

A bit of Excel news for you today! Three regular expression functions launched in the beta build of Excel this past week.

The announcement article is here, and they’ll be adding to it as additional features are tested.

Regular expressions (regex) match patterns in text. MDN refers to them as

They’re used in coding, and their syntax is notoriously difficult to understand, let alone master.

How are we to use them in spreadsheets, then?

Well, they’ve been in Google Sheets for a while now. Not to be outdone, Excel is adding the same three functions, though they’ve changed the name of one and are adding a bit more functionality to them all…

Here’s a walkthrough of all three functions. Below it, you’ll find details on each

The functions

REGEXTEST

REGEXTEST() determines whether any part of the text matches the regular expression pattern. In Google Sheets, this is called REGEXMATCH(), but they do the same thing.

In my sample workbook from the video demo, I test for valid email addresses using the REGEXTEST() function.

=REGEXTEST(D2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")

REGEXEXTRACT

REGEXEXTRACT() extracts a strings that match the regex pattern.

In my sample workbook, I extract last names from cells that have dirty data in them - a mix of names and numbers.

=REGEXEXTRACT(C2, "^[A-Za-z]+")

REGEXREPLACE

REGEXREPLACE() replaces the strings that match the regex pattern.

In my sample workbook, I replace the first five digits of social security numbers with asterixis using REGEXREPLACE().

=REGEXREPLACE(E2, "^(\d{3}-\d{2})", "***-**")

Excel Functionality

Excel is adding case sensitivity optional arguments to their functions. Regular Expressions themselves can test for this, however, it’s a welcome addition to have a toggle rather than making sure to bake this in to the regular expression. It defaults to case-sensitive.

Microsoft also says that there will soon be a way to use regex inside XLOOKUP and XMATCH functions via a new ‘match mode’ argument.

That ought to be neat!

Microsoft Insider Program

If you want to try out these inside of Excel, you’ll need to be running a beta build of Excel. Doing this isn’t hard.

You’ll need to join the Microsoft 365 Insider Program. Check out their article here.

Open up Excel, Go to File, and then in the bottom left corner, select Account.

This will open up product information about your subscription.

From here, you can select Microsoft insider and then choose the Beta Channel from the drop down inside the menu that will pop up.

📌A Big Final Tip

Regular expressions are impossible. 🤣🤣

Use AI to figure them out. I prompted ChatGPT (the free version is perfectly good for this) to help me figure out the expressions I needed for the video demo that I made.

Be specific on your use case, and give the prompt an example of your expected outcome.

For instance, here’s what I told ChatGPT for the social security number example:

i need a regular expression that replaces the first five digits in a social security number with "*"'s. For example, given the number 123-45-6789, I need the regular expression to return ***-**-6789

Thank you so much!

It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!

Would love to say hi. Here are the best places to find me:

• 📺️ Videos on YouTube

• 💾 Products on Gumroad

• ✉️ Connections on LinkedIn