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?

Continue reading “Regular Expressions Finally Come To Microsoft Excel” →