Built-in RegEx / Regular Expressions Functions to Search, Extract, and Replace
I've constantly found myself installing and re-installing add-ins that add RegEx Functions to Excel. Everyone I introduce them too finds them extremely useful. There's over half a million results in google for the query ["excel" ("regular expression" OR "regex")].
Thanks for the suggestion! We’re fans of Regex and its ability to parse text too.
If you’d like to see Regex support in Excel, please keep the votes rolling in.
- JoeMcD [MS XL]
could you share the script lab snippet for that?
I've just done it for one example in my sheet.
I also propose to replace VBA with Perl!
Personally, I am not optimistic as to whether it will ever be introduced.
Considering all threads on both stackoverflow and answers.microsoft and all posts here, as well as the fact that there are no 'fully featured' regex add-ons, I guess regex support within Excel probably is too complicated...??
I STRONGLY recommend that, when this feature is implemented, Microsoft includes a link to a RegExp playground in the documentation of the function. Microsoft could either use https://regexr.com/ or they could use the completely open-source nature of that site to easily make a copy of that site specific to Excel.
One plus of this feature is that it will mean that some advanced Excel courses will start to teach RegExp, which, in the long run, is far more practical skill than VBA. (Not to diss the importance of VBA.)
Patrick Matthews commented
Expanding on this, Excel should be able to support the following regexp operations:
1) Determine whether a source string matches a regexp pattern
2) Return an array of regexp matches from a source string (or just the nth match)
3) Return info on regexp matches within a source string, such as starting position and length of each match
4) Return an array of regexp submatches from a source string (or just the nth submatch)
5) Perform a find/replace operation on a source string based on regexp
#1 and #2 in the list above can be handled with the RegExpFind UDF described therein
#3 can be handled with RegExpFindExtended
#4 can be handled with RegExpFindSubmatch
#5 can be handled with RegExpReplace
Still waiting for this after two years and wondering why it is so difficult to implement. We've got Regex in VBA after all, so most of the parsing code must be there and ready to re-use.
We've had a lot of other changes to Excel in those two years - many of which don't appear to have been requested by users (at least judging by this forum) but we are still awaiting some of the more requested ones here!
FWIW - personally, when a regex match/replace is required, I copy/paste the column into a text editor, like EditPadPro, perform the action there and copy-paste it back into Excel. This may not work correctely in case of filtered data though. Nothing more than a simple workaround.
Kenneth Barber commented
Regular expressions are essential for the replacement of the -IFS functions (e.g. SUMIFS) with FILTER, since the -IFS functions can handle wildcards but FILTER cannot. With regular expressions in place, we can get even more filtering power from SUM(FILTER()) compared to SUMIFS().
This is an absolute must. Tired of having to copy data to another spreadsheet editor just to find something.
Walter Pelowski commented
Having regex capabilities would dramatically simplify a lot of the daisy-chaining of M functions or Excel functions I have to string together as well as providing capabilities that I can't even achieve (or don't know how to achieve) with current methods.
I hope that if/when you implement regexs that you consider a flavor that supports positive and negative lookbehinds and lookaheads.
Dale Phurrough commented
Mark Fitzpatrick commented
It should be in both Native Excel and M.
Dynamic Arrays will likely increase the need and power of a RegEx function.
Any Mouse commented
Since REGEX is such a multifaceted animal, I wish to express my interest in at least having the functionality to cull a formatted sub-string from another cell. Something like =LEFT(A5, 11), except that I don't know where in the string the 11 characters that I want will be (based on format). This is for pulling specific information out of user entered data. The point is, I am not looking for find or find/replace, which is interactive, or for a filter; I am looking to extract consistently formatted information from inconsistently entered data, applied in every row of data provided.
Google Sheets has this.. y'all want me to use Google Sheets or excel?
Definitely a modern feature that Excel should have!
Absolutely. Today I needed a function to remove multiple unwanted characters, then replace spaces with dashes.
The Google sheets version will replace multiple spaces with a single dash. The Excel version will remove double dashes, but that is all.
Perry Wheeler commented
Support this 100%.
Don't over-complicate or attempt to simplify it, just implement a =REGEX(String) formula based in one of the industry-standard models (PCRE maybe?). I have a VBA version that I use all the time, but, as it uses the MS RegEx library 5.0 it is an incomplete implementation (no look-behind).
For me, this is probably the single biggest missing game-changer function in Excel and is the one that I most have to build workarounds for. I know there are those that argue that most functionality can be duplicated using existing functions but, once you get past the basics, these become horrifically complex, difficult to debug, nested messes to emulate something that can be done in a few chars of regex.