Regular expressions are a powerful way to detect and modify data strings, but Microsoft Excel has never natively supported them. That’s finally changing, with the introduction of regular expression functions in Excel.
Regular expressions, also known as “regex” or “regexp,” are strings used to match patterns in data strings. For example, the regular expression “\b[aA]\w*\b” could match every word in a string that starts with the letter “A,” or you could use “\b-?\d+(\.\d+)?\b” to match any number in a string of text. You can then remove, replace, or extract the matches as needed. Regular expressions can be difficult to read and understand, but tools like Regex101 can be helpful as a guide, and generative AI chatbots like ChatGPT and Microsoft Copilot are great at writing them.
Microsoft announced three new functions that use regular expressions, available now in the Excel Beta Channel. You can use REGEXTEST to check if the supplied text matches a regex pattern, REGEXEXTRACT to extract a match, and REGEXREPLACE to replace a match.
Microsoft Excel already has functions for many of the popular use cases for regular expressions, but not necessarily all of the use cases. Some people might also be more familiar with the syntax for regular expressions than Excel’s native functions, or they want to share regular expressions across different software (like a Python script and an Excel workbook). Until now, you needed to use workarounds like macros or add-ins to write macros, which aren’t available on all platforms.
Microsoft also plans to add support for regular expressions to Excel’s XLOOKUP and XMATCH functions. Presumably, that will allow searching across entire Excel workbooks for regular expression matches. There are a lot of potential uses for regular expressions, and it’s great to see Microsoft finally embracing them in Excel without the use of third-party tools or workarounds.
The new functions are available in the Excel Beta Channel, starting with version 2406 (build 17715.20000) on Windows and version 6.86 (Build 24051422) on Mac. Microsoft says these are still preview functions that could change before being broadly released, so don’t use them in important documents for now.
Source: Microsoft 365 Insider
