Microsoft Updates Excel Preview with Regex Support in XLOOKUP/XMATCH Functions

Since May 2024, Microsoft has been testing the addition of regular expression (regex) support in the Excel suite for Microsoft 365 Insiders. The new regex function allows users to batch match content through regular expressions to enhance efficiency.

Today, Microsoft has introduced regex support in two more functions: XLOOKUP and XMATCH. This update enables users to define patterns in search sequences using regular expressions.

From the Microsoft 365 Development Team:

You can now use regular expressions within the existing XLOOKUP and XMATCH functions by utilizing the new [match_mode] = 3 and incorporating regex patterns as the lookup_value.

This allows XLOOKUP and XMATCH to match with parts of the text in cells or any other text patterns that can be described with regular expressions.

In essence, with the integration of regex into these two functions, users can leverage regular expressions to boost efficiency. What previously required multiple steps can now be conveniently achieved with regex matching, assuming familiarity with regex usage.

Microsoft notes that if users are unable to find the tags needed for the regex functions, they can enter prompts in Copilot, allowing AI to assist in crafting regular expressions, thus offering a learning opportunity.

Common Regular Expression Symbols:

"[0-9]" # Any number between 0-9
"[az]" # Any letter between a-z
"." # Use. Represents any character (number or letter, etc.)
"a" # Specific value such as a
"a*" # Matches the preceding character a zero or more times (even if a does not exist, it can be matched successfully because it can be zero times)
"a+" # Matches the preceding character a one or more times (at least one a is required to match successfully)

It's important to note that these features are currently being tested in the Microsoft 365 Insider version only. Users of the official release cannot utilize regex support yet; it will be available once rolled out to the general release.