There are two types of people in the world: those who have no idea what a regular expression is, and those who not only know what they are but can compose them on the fly and tend to use them in situations where they’re clearly not called for. And it’s that latter camp, of which we consider ourself a proud member, that is rejoicing with the announcement that Microsoft is adding regular expression support to Excel.
Or perhaps not rejoicing so much as wondering what took so long. Yes, regular expressions have been part of VBA for a while now, but the new functions allow you to use regexes right in the spreadsheet grid. There are plenty of caveats, of course. The big one is that this is still in beta at this time, so you have to do some gymnastics to enable it, if you’re even allowed to in the first place. Second, support appears limited to three functions at the moment: REGEXTEST, which provides a logical test of pattern matching; REGEXEXTRACT, which returns a substring that matches a pattern; and REGEXREPLACE, which substitutes a string for a pattern. The video below walks through how to use these functions within spreadsheets.
What’s also unclear now is what flavor of regular expressions is supported. There are a bewildering number of entities in the regex bestiary — character classes, positional indicators, quantifiers, subexpressions, lazy and greedy matches, and a range of grouping constructs that perplex even regex pros. One hopes these new functions will support one of the existing regex standards, but Microsoft is famous for “extending and enhancing.” Then again, regex support has been in the .NET Framework for years and is pretty close to the Perl standard, so our guess is that it’ll be close to that.
If you fall into the “What’s a regex?” camp but want to change that, why not get your grep on?
The guy in the video pronounced regex wrong. You’re supposed to roll the “r.”
Per the function documentation: “All regular expressions for this function, as well as REGEXEXTRACT and REGEXREPLACE use the PCRE2 ‘flavor’ of regex.”
Grr… Why no regex support in Word VBA? Fancier search and replace in Word is desperately needed.
Just add a refence to the VBScript RegEx library.
Simple, VB is deprecated and scheduled for removal.
From MS themselves: https://techcommunity.microsoft.com/t5/windows-it-pro-blog/vbscript-deprecation-timelines-and-next-steps/ba-p/4148301
Oh joy, can we make it sound off a loop of a dental drill gringing into teeth as we use it … preferably overriding all volume control to max please
Some people, when confronted with a problem, think “I know, I’ll use regular expressions.” Now they have two problems.
– jwz
True that!
It’s the 555 of the programming world.
And some people, when confronted with a problem, think “the most suitable way to handle this is with a regular expression.” Now they have zero problems and spent 1/100th the time dicking around with bloated, unnecessary code.
I have wondered for a while why MS haven’t had regex support as standard in their products in the same way that others have had *for decades*!
Sure, you can shoot yourself in the foot with regexes but they can be pretty darn powerful when pattern matching and global/local search and replace.
On the flip side, all those people who currently (mis)use Excel documents as databases, there’ll be another group of people who think “oh wow! how wonderful are these regex things! I can use them in my macros to do x!”. Can you imagine the carnage if Excel supported native regex right out of the box? One misplaced backslash and it’s bye-bye quarterly accounts figures.
‘”all those people who currently (mis)use Excel documents as databases”
+ 1 quadrillion
“all I did was update from the 2k version to the 2016 version…”
“what do you mean by ‘ALL the linked sheets”
“..and I need to put the equipment order in by 3pm..”
“what’s ‘Access’?”
“what the hell is a Progress database..”
“Well yes, our entire ERP system is Excell, one of the engineers started it, is that a problem?”
“…it was always just on this one computer that was over there, I’m sure someone has a copy….
“the horror….. the horror”
(it guy struggling with childproof cap on the zanax)
“There are two types of people in the world: those who have no idea what a regular expression is, and those who not only…”
For those who don’t know what regex are, the author of this article isn’t going to tell you. It’s like a big finger pointing and laughing “haha, I know something you don’t know”. But there is a third kind of people regarding regex that the author of the hackaday article complete ignores and that are the people that don’t care what regex are.
Regexen
Regex is evil and my bane. Even when it’s verified to be “correct”, it’s incorrect, somehow. I stick to writing my own parsers – less pain.
I mean, I’m just not so keen on the whole sacrificing of innocent virgin goats on an altar to get my program to work. I know the boss put the altar there in the back next to the coffeemaker and it’s free for everyone to use as long as you clean up afterwards… but… no.
When you first encounter these, they make your head hurt. Eventually they still make your head hurt but you can use them. What regular people need is a plain text representation for compilers that translates to the short hand of regex expressions.
No, there’s definitely a third type of person – someone who knows what they are, but has to Google how to do even the simplest things with them. It’s me, I’m that third type of person.
Indeed. I use regex101.com a lot to build and verify them. And blessed be the languages which support ‘raw strings’.
Great! No more handwritten MID/FIND/IF/LEN parser shenanigans as a stand in for regex. Now if only they would update the formula eval window to a sane usable window and font size. While we’re dreaming, add Vim keybindings.
You can resize the formula window.
As for those wondering about use in Word, or where they’ve been for so long: LibreOffice. Which also has the handy feature of returning what was matched/replaced via the interactive find tool and showing it in a dialog for you to review in case you need to undo.
The formula window or the formula evaluation window? Being able to resize the formula evaluation window would be great, but so far Excel 365 doesn’t seem to support that.
Resize the formula evaluation window in Excel…Nope, not in this version of the multiverse, formula bar only.
As for using LibreOffice and the extra bells and whistles it comes with, some folks don’t have the luxury or authority to make that switch in software; that is to say, if they want to keep their job afterward.
Is this Microsoft’s new
REGEMBRACE
REGEXTEND
REGEXTINGUISH
policy?
s/^REG.*$/\1EX/
There is only regex!
If you’re in the IT field, there’s a law that states ever job has that one Regex person that everyone goes to when they need a regular expression. If you don’t know who that person is, it’s probably you.
@Dan Maloney said: “What’s also unclear now is what flavor of regular expressions is supported. There are a bewildering number of entities in the regex bestiary — character classes, positional indicators, quantifiers, subexpressions, lazy and greedy matches, and a range of grouping constructs that perplex even regex pros. One hopes these new functions will support one of the existing regex standards, but Microsoft is famous for “extending and enhancing.” Then again, regex support has been in the .NET Framework for years and is pretty close to the Perl standard, so our guess is that it’ll be close to that.”
Perl Compatible Regular Expressions (PCRE) is a library written in C, which implements a regular expression engine, inspired by the capabilities of the Perl programming language. Philip Hazel started writing PCRE in summer 1997. PCRE’s syntax is much more powerful and flexible than either of the POSIX regular expression flavors (BRE, ERE) and than that of many other regular-expression libraries.
IMHO adopting PCRE is surely the way to go. There’s lot’s more to peruse on the Wikipedia page on PCRE.[1]
1. Perl Compatible Regular Expressions
https://en.wikipedia.org/wiki/Perl_Compatible_Regular_Expressions
@Dan Maloney said: “What’s also unclear now is what flavor of regular expressions is supported.
“All regular expressions for this function, as well as REGEXTEST and REGEXREPLACE use the PCRE2 ‘flavor’ of regex.”
https://support.microsoft.com/en-us/office/regexextract-function-4b96c140-9205-4b6e-9fbe-6aa9e783ff57
I use https://extendsclass.com/regex-tester.html to create my regex. AI is not great for that yet …