Microsoft Excel introduces new REGEX functions
2 min. read
Published on
Share this article
Improve this guide
Read our disclosure page to find out how can you help MSPoweruser sustain the editorial team Read more
Key notes
- Microsoft Excel introduces new REGEXTEST, REGEXEXTRACT, and REGEXREPLACE functions for text manipulation using regular expressions.
- These functions are currently in beta testing and offer greater control over finding, extracting, and replacing text patterns.
- REGEXTEST checks if text contains a pattern (TRUE/FALSE), REGEXEXTRACT retrieves matching text (various options), and REGEXREPLACE substitutes patterns with new text.
Microsoft Excel is increasing its text manipulation capabilities by adding three new functions that use regular expressions (REGEX).
Regular expressions, or REGEX, are like shortcuts for finding and working with specific pieces of text. Imagine you have a code that helps you quickly find certain words, numbers, or any pattern in a big book. That’s what regex does with text data on computers. It uses special symbols to help you find what you’re looking for. The new functions are:
REGEXTEST: This function checks if a piece of text has any part that fits a specific pattern. If it finds something that matches, it says TRUE, but if not, it says FALSE.
For example, REGEXTEST can be used to verify if a column contains email addresses by using a pattern that matches the common email structure.
REGEXEXTRACT: This tool pulls out specific parts from text data that fit a certain pattern. It has different ways to get the information you want.
- First Match: Returns the first occurrence of the matching substring.
- All Matches: Returns a list of all substrings that match the pattern.
- Capture Groups: By incorporating parentheses within the pattern, users can define specific parts to be captured during the match. REGEXEXTRACT can then extract only these captured groups.
REGEXREPLACE: This function finds parts of the text that fit a specific pattern and changes them to something else that the user decides. It’s a quick way to change text according to certain rules.
For instance, REGEXREPLACE can be used to anonymize phone numbers by replacing the initial digits with asterisks.
These new functions are currently available for testing within the Microsoft Excel Beta Channel. The official release date is yet to be announced.
Microsoft has also hinted at future integration with XLOOKUP and XMATCH functions.
These functions are currently available to Beta Channel users running:
- Windows: Version 2406 (Build 17715.20000) or later
- Mac: Version 16.86 (Build 24051422) or later
Here is other new Excel news:
Ink to Text Pen tool now available in Excel for Windows
Users can soon generate multiple formula columns from a single prompt on Excel via Copilot
More here.
User forum
0 messages