Excel acquires 3 new regular expression functions

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Excel acquires 3 new regular expression functions
If you love Excel and are also a fan of regular expressions, your Christmas has come early! And if like me you don't know how to create regular expressions, you can just get an AI tool to build them for you ...

Posted by Andy Brown on 05 June 2024

In this blog

Which version of Excel do you need?
Getting regular expressions
The REGEXTEST function
The REGEXEXTRACT function
The REGEXREPLACE function

Excel has 3 new regular expression functions!

Function	What it does
REGEXTEST	Tests whether a cell entry matches a regular expression
REGEXEXTRACT	Picks out the part of a cell which matches a regular expression
REGEXREPLACE	Replaces the string of text which matches a regular expression with something else

If you don't know how to write regular expressions to pick out text matching particular patterns, fear not - you can always get an AI tool to create your regular expressions for you, as this blog shows.

Which version of Excel do you need?

I don't want to be accused of showing off beautiful trinkets which turn out not to be available, so let me start by explaining when you'll be able to use these functions. If you choose the File menu in Excel you'll be able to see this option:

You can then choose to show your Account details.

You can then see which 365 update channel you're on, and which version of Excel you're using:

I'm on the Beta Channel, and using Excel version 2406 (the minimum version number you'll need to support regular expression functions).

If (as is likely) you're still on version 2405, you can change your update channel as described in this blog.

Getting regular expressions

The easiest way to get a regular expression (assuming you're not a regex guru already) is to use an AI tool like ChatGPT. Here's my initial attempt to get a regular expression to pick out all film titles containing a digit but not starting with one (you can see this in use further down in this blog):

My initial prompt.

ChatGPT slightly misunderstood this (my bad, I suspect), so I tweaked it:

A revised prompt to get exactly what I need.

I then got a regular expression I could copy:

You can just click on the link shown to copy this.

ChatGPT provided an explanation of how this works if I'm interested:

How this regular expression does what it does.

And most usefully, some examples of what the regular expression would and would not pick out:

How AI can do this heaven only knows!

The REGEXTEST function

Armed with my new-found knowledge of regular expressions, I can now put them to the test in Excel. Let's start by picking out all film titles which contain (but don't start with) a digit:

This displays TRUE only if the film title contains a digit, but doesn't start with one.

Here's the formula:

=REGEXTEST(

B2,

"^(?!\d).*\d.*"

)

You might like to think how you would do this without the REGEXTEST function, to persuade yourself just how much time this could save you!

The REGEXEXTRACT function

This clumsily-named function not only finds a regular expression, but extracts it. The following example would show the middle name of any film director who has one:

Here the formula would show Yuh for Jennifer Yuh Nelson, since she has a middle name.

Here's the formula used above:

=REGEXEXTRACT(

C6,

"(?<=\s)\b\w+\b(?=\s)"

)

The regular expression syntax was once again provided by ChatGPT:

The question I posed.

This is the sort of thing I've done so many times in Excel, and it's always been cumbersome - I'm loving this elegant and simpler alternative approach.

The REGEXREPLACE function

The following example replaces any middle name with an X (the REGEXREPLACE function is useful for redacting sensitive text or numbers in this way):

This formula is like the one above, but it doesn't just find matches to the regular expression, it also replaces them.

Here's the formula used above:

=REGEXREPLACE(

C6,

"(?<=\s)\b\w+\b(?=\s)","X"

)

You can find other examples of the use of REGEXREPLACE (and the other new regular expression functions) at the Microsoft release announcement site.

Finally, Microsoft have announced that regular expressions will be integrated within the XLOOKUP and XMATCH functions soon, which really could save time!

Some other pages relevant to the above blog include:

Microsoft Excel classes

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Which version of Excel do you need?

Getting regular expressions

The REGEXTEST function

The REGEXEXTRACT function

The REGEXREPLACE function

Head office

London

Manchester