Excel 365 is getting new functions that bring the power of Regular Expressions (RegEx) into spreadsheets. It’s HUGE news and a big step forward for Excel.
We’ve spent some time trying out the new functions and we’re genuinely excited by the possibilities. Unfortunately, almost six months have passed since the Regular Expression functions arrived in the beta Insiders releases of Excel 365 with no sign of them being released to public versions.
These three Regular Expression (Regex) functions are now in previews of Excel 365 for Windows and Mac.
- REGEXTEST: Checks if any part of supplied text matches a regex pattern and returns True/False.
- REGEXEXTRACT: Finds parts of text that matches a regex pattern and returns that text.
- REGEXREPLACE: Searches for a regex pattern and replaces it with different or reordered text.
Regular Expressions are a way to find (and replace) within text strings in ways that aren’t possible with standard Find() functions. Regex go way beyond the familiar * and $ wildcard symbols.
If you already use Regex then you’ll be delighted to know that such a common and powerful tool is finally coming to Excel. It makes available what was, up to now, complex or impossible tasks.
RegexTEST()
The simplest of the new Regex functions, RegexTest() will test a pattern against some text and tell you if there’s a match or not.
It’s a direct way to test a regex pattern before moving to the other functions.
RegexEXTRACT()
RegexExtract() Copies any text matching the pattern, for example extracting the area code only from a phone number.
Using Regex with phone numbers can be VERY complicated, this is a trivial and limited example.
RegexREPLACE()
Regular Expressions can’t just find text, it can replace or rearrange it. In Excel with RegexReplace() you can, as a common example split names into first and second parts then reverse them with a comma in-between.
=REGEXREPLACE([@Customer],"([A-z]+) ([A-z]+)","$2 , $1")The brackets () define each segment then they are inserted/replaced using $1 $2 etc in the third parameter. NOTE: not the usual \1 \2 format that’s normally used by Regex.
Patience, grasshopper
Exciting as these regex functions are, use them with caution for the moment. They are only in preview versions of Excel 365 for good reason. Implementing regular expressions in Excel is difficult and painstaking work. Let’s give the ‘softies the time and feedback they need to get it right.
Or in Microsoft corporate speak:
“… results may change substantially before being broadly released. … we do not recommend using these functions in important workbooks until they are generally available. “
(they mean general availability of the functions, not the workbooks) <g>.
Notes
A few things we’ve noticed so far …
- All regex patterns are text strings not hard coded. That means you can get the regex pattern/s from other cells to make the complete function or use a single pattern in a named range for consistent tests. See the example below where cell A2 is used as the regex pattern for all the tests in the table.
- The Regex functions work with number cells but be careful. It seems they use the stored value not the number as displayed. That explains the seemingly incorrect results in rows 5 and 7 below. For the moment, it’s safer to use the ‘number to text’ function Text() rather than reference numeric cells directly, see rows 6 and 8.
- Microsoft’s examples use the pattern
[A-Z][a-z]to find any letters upper or lower case. That works but we prefer the shorter[A-Za-z]which also works and is a little shorter.
Hooray! 🐨 It’s a Regex standard
Big Koala Stamp🐨 of approval to the Microsoft ‘softies who chose (likely pushed) for an open, known regex standard.
RegexTEST(), RegexEXTRACT() and RegexREPLACE() all use PCRE2 or Perl-compatible Regular Expressions … Wikipedia. That’s an open standard that’s well documented and widely used.
Most important regex testing services like https://regex101.com/ let you choose the regex ‘flavor’ with PCRE2 available. The Internet is crawling with PCRE2 specific advice and examples.
That’s in stark contrast to other Microsoft versions of Regular Expressions in Word and VBA which are proprietary and sadly lacking adequate documentation. Word users have struggled for years with the finer details/bugs in it’s ‘Use Wildcards’ (Regex) option.
There’s more coming ….
If you thought that Regex would be great in the Excel ‘X’ functions XLOOKUP and XMATCH then Microsoft is way ahead of you.
Coming to both functions is a new parameter in the ‘match mode’ argument. A regex pattern can be supplied as the ‘lookup value’ to find text in a list or table.
We can’t wait …
What about Power Query?
At risk of seeming ungrateful … Power Query is crying out for Regular Expressions. We’ve seen advice around adding regex into Power Query but it ain’t pretty.
With the new functions in Excel and soon XLookup/Xmatch … could the Power Query team please add RegEx PCRE2 ? We promise another Koala Stamp for the dev team <g>.
More Word Find / Search tricks
A better ‘anonymize a Word document’ solution
