New Regular expression (Regex) functions in Excel
Regular expressions, or ‘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 supplied text matches a regex pattern.
REGEXEXTRACT: Extracts one or more parts of supplied text that match a regex pattern.
REGEXREPLACE: Searches for a regex pattern within supplied text and replaces it with different text.
Let’s dive in and take a look at each function in more detail.
REGEXTEST
REGEXTEST checks whether the pattern matches any part of the provided text, always returning TRUE or FALSE.
Worksheet showing a REGEXTEST function
Checking whether the strings in column C contain numerical digits, using the regex pattern “[0-9]”
The full signature is: REGEXTEST(text, pattern, [case_sensitivity])
Learn more
REGEXEXTRACT
REGEXEXTRACT returns substrings of text that match the pattern provided. Depending on the return mode, it can return the first match, all matches, or each capture group from the first match.
Worksheet showing a REGEXTRACT function
Extracting names from text using the pattern “[A-z]+ [A-z]+”, which matches two groups of alphabet characters separated by a space
The full signature is: REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
Learn more
REGEXREPLACE
REGEXREPLACE looks for substrings of text that match the pattern provided, and then replaces them with a replacement string.
Worksheet showing a REGEXREPLACE function
Replacing the first three digits of each phone number with ***, using the pattern “[0-9]{3}-”, which matches against three numerical digits followed by “-”
The full signature is: REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
Learn more
Regex coming soon to XLOOKUP and XMATCH
We will also be introducing a way to use regex within XLOOKUP and XMATCH, via a new option for their ‘match mode’ arguments. The regex pattern will be supplied as the ‘lookup value’.
This will be available for you to try in Beta soon, at which point we’ll update this blog post with more details.
Tips and tricks
When writing regex patterns, you can use symbols called ‘tokens’ that match with a variety of characters. Here are some useful tokens to get you started:
“[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”
ليست هناك تعليقات:
إرسال تعليق