Microsoft Introduces RegEx Functions in Excel for Batch Fuzzy Matching
Brook.X
For Excel enthusiasts, there's an enticing new feature on the horizon: Microsoft is currently trialing Regular Expression (RegEx) functions in the Excel Preview.
Regular expressions, also known as regex, offer a way to use relatively straightforward string patterns for fuzzy matching of specific content. Once a match is found, other functionalities, such as replacement, can be used to substitute all successfully matched content with new information.
Developers frequently utilize regular expressions for bulk data processing. Now, Microsoft is bringing regex functions to Excel, aiming to boost efficiency for users managing vast amounts of content.
Currently Tested RegEx Functions in Microsoft 365 Insider:
- REGEXTEST (Test): This function performs simple tests, returning a Boolean value (True or False) upon a successful match.
- REGEXEXTRACT (Extract): Designed for matching textual string content, this function can return the first match, all matches, or every group within the first match, depending on the pattern specified.
- REGEXREPLACE (Replace): Building on the REGEXEXTRACT function, it replaces matched content with new information after a successful match based on a secondary pattern.
Expansion into XLOOKUP and XMATCH:
Beyond their standard applications, Excel's regex functions will also be usable within XLOOKUP and XMATCH, allowing the addition of new parameters through matching patterns and using regular expressions as lookup values.
This integration enables users to combine different functions and use them together, potentially increasing Excel's operational efficiency, despite the learning curve.
Future Insights:
Microsoft plans to release a dedicated blog post explaining how to utilize these features. Those interested should follow the official Microsoft 365 Insider blog for updates.
Version Support:
The regex functions are currently supported in Excel for Windows version 2406.17715.20000+ and Excel for Mac version 16.86.24051422+. Following the completion of tests, these features will be rolled out to the official version of Microsoft 365 Excel.
