Microsoft introduces new Regex modes for the XLOOKUP and XMATCH functions in Excel

Microsoft has rolled out new regex modes for XLOOKUP and XMATCH functions in Excel for Windows and Mac users. Regex, or regular expressions, are sequences of characters that help define search patterns. Imagine looking for a needle in a haystack; regex is the magnet that makes the needle light up.

In a blog post, Microsoft says you can use regex within XLOOKUP and XMATCH functions by setting the [match_mode] = 3 and using a regex pattern as the lookup_value. This means you can now match parts of text in a cell or any other text pattern that can be described with regex.

For those who might be new to this, standard tokens include “[0-9]” for any numerical digit, “[a-z]” for a character in the range of a to z, and “.” for any character, among others. And if you’re feeling stuck, Microsoft suggests trying out Copilot for regex patterns.

Before diving in, it’s important to note that these functions are currently in the Beta channel. Windows users will need Version 2408 (Build 17931.20000) installed, while Mac users should have Version 16.89 (Build 24080715).

As with all things in the beta phase, Microsoft advises against using these functions in critical workbooks until they are generally available. It’s a bit like testing the waters before taking the plunge.

So, what do you think? Are you ready to give these new regex modes a whirl and see how they can transform your Excel experience? Whether you’re a data analyst, a student, or just someone who loves organizing data, this update could be a game-changer.

You can read more about Microsoft Excel’s new Regex modes here.