18. REGEX Functions

A regular expression (regex) is a pattern that describes what the piece of text you need looks like: "four digits", "something@something.com". Microsoft 365 has three functions for it (they do not exist in Excel 2021 and earlier):

• REGEXTEST(text, pattern, [case_sensitivity]) returns TRUE if the pattern is found. Case sensitivity: 0 means match case (default), 1 means ignore case;
• REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) pulls out what it finds. Mode 0 returns the first match, 1 returns all matches (they spill down), 2 returns the groups in parentheses of the first match (they spill to the right);
• REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) replaces matches. In the replacement, $1, $2 are the contents of the parentheses.

If the pattern is not found, REGEXEXTRACT returns #N/A. Wrap it in IFNA.

The most useful syntax:

• \d is a digit, \D is a non-digit, \w is a letter, digit or _, \s is a space;
• [a-z] is one character from a set, [^0-9] is any character except the set;
• + means one or more, * means zero or more, {4} means exactly four;
• ^ is the start of the text, $ is the end, \. is a real dot (a plain . is any character);
• ( ) is a group, | means "or": (LLC|Inc)$.

Excel uses the PCRE2 dialect, the practice grid uses JavaScript regular expressions. The simple patterns in this lesson work the same in both. In Excel you can pass a whole range and the result spills; in the practice grid, pass a single cell and fill the formula down.