Microsoft 365 Insiders can now use REGEX functions in Excel
The features are introduced on May 21, 2024
5 min. read
Published on
Share this article
Read our disclosure page to find out how can you help Windows Report sustain the editorial team. Read more
Jake Armstrong, the product manager on the Excel team, announced that Microsoft 365 Insiders will be able to use three new functions: REGEXTEST, REGEXEXTRACT, and REGEXREPLACE, which employ Regular Expressions to help parse text.
With the announcement, Microsoft has added a warning, which says:
These are preview functions. Their signature and results may change substantially before being broadly released, based on your feedback. So, we do not recommend using these functions in important workbooks until they are generally available.
What are Regular expression (Regex) functions?
As mentioned in the official announcement, Regular expressions:
Regex are sequences of characters that define search patterns, commonly used for string searching and text parsing. They are incredibly versatile and are often used to check if a string contains a certain pattern, extract substrings that match the pattern, or replace substrings that match the pattern.
The new regex functions we are introducing are:
- REGEXTEST: Checks if any part of the supplied text matches a regex pattern.
- REGEXEXTRACT: Extracts one or more parts of the supplied text that match a regex pattern.
- REGEXREPLACE: Searches for a regex pattern within the supplied text and replaces it with different text.
REGEXTEST
It checks if the pattern matches any part of the provided text, matches the pattern. If it matches, it says True, but if it fails, it returns with False.
The syntax of the function is: REGEXTEST(text, pattern, [case_sensitivity])
| Argument | Description |
|---|---|
| text(required) | This parameter can either be the text string you want to match against or a reference to a cell that contains the text for comparison |
| pattern(required) | This parameter represents the regular expression (“regex”) that defines the pattern of text you wish to match. |
| case_sensitivity | It determines whether the match is case-sensitive. The match is case-sensitive by default. You can enter one of the following values 0 – Case sensitive 1 – Case insensitive |
REGEXEXTRACT
REGEXEXTRACT lets you retrieve text from a string using a supplied regular expression. You can choose to extract the first match, all matches, or capture groups from the first match.
The syntax of the REGEXEXTRACT function is – REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
| Argument | Description |
|---|---|
| text(required) | The parameter contains the text or the reference to a cell containing the text you want to retrieve strings from. |
| pattern(required) | The parameter represents a regular expression (“regex”) that defines the pattern of text you wish to extract. |
| return_mode | A number that describes what strings you want to retrieve. The return mode is 0 by default. The possible values areas listed: 0: Return the first string that matches the pattern 1: Return all strings that match the pattern as an array 2: Return capturing groups from the first match as an array |
| case_sensitivity | It determines whether the match is case-sensitive. The match is case-sensitive by default. You can enter one of the following: 0: Case sensitive 1: Case insensitive |
REGEXREPLACE
This function allows you to replace text from a string with another string on the basis of supplied regular expression.
The full syntax of REGEXREPLACE is – REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
| Argument | Description |
|---|---|
| text(required) | The parameter has text or the reference to a cell containing the text you wish to replace strings within. |
| pattern(required) | It is a regular expression (“regex”) that defines the pattern of text you want to replace. |
| replacement(required) | The text you wish to replace instances of pattern. |
| occurrence | This parameter defines which instance of the pattern you want to replace. The occurrence is 0 by default, which replaces all instances. A negative number replaces that instance, searching from the end. |
| case_sensitivity | It determines whether the match is case-sensitive. The match is case-sensitive by default. You can enter one of the following: 0: Case sensitive 1: Case insensitive |
Tips & tricks offered to use Regex functions easily
You can use Symbols, also known as Tokens, in regex patterns that match with various characters. Microsoft has listed some useful tokens:
- [0-9] – any numerical digit
- [a-z] – a character in the range of a to z
- “.” – any character
- a – the a character
- a* – zero or more a
- a+ – one or more a
You can also ask Bing Copilot for regex patterns. Microsoft also announced that they would be adding functionality for match mode arguments to use regex within XLOOKUP and XMATCH in the near future. In this regex pattern, it will be available as the lookup value.
These functions are now available for Beta Channel users with either Windows Version 2406 (Build 17715.20000) or later and Mac Version 16.86 (Build 24051422) or later.
Recently, Microsoft added a functionality that lets you change your handwriting into text on Excel. To know more, check out this article.
What do you think about these new regex functions? Share your views with our readers in the comments section below.
User forum
0 messages