Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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 ... |
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.
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.
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!
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!
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 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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.