Level 3REGEXReading, 6 min
Regular expressions: find text by its shape
A regular expression is a pattern for text. Instead of "the 5th to 12th characters", you describe what you're looking for: "the digits after #", "anything that looks like an email", "whatever comes after utm_campaign=". The data can shift around; the pattern still finds it.
| Function | Answers | Example |
|---|---|---|
REGEXMATCH(text, pattern) | does it match? TRUE or FALSE | =REGEXMATCH(A2, "@gmail\.com$") |
REGEXEXTRACT(text, pattern) | what matched? | =REGEXEXTRACT(A2, "\d+") |
REGEXREPLACE(text, pattern, new) | swap every match | =REGEXREPLACE(A2, "[^0-9]", "") |
The pieces you'll use most:
| Write | Matches |
|---|---|
\d \s . | a digit, a space, any character |
[abc] [^abc] | one of these characters, or anything but these |
+ * ? {3} | one or more, zero or more, optional, exactly 3 of the thing before |
^ $ | the start and the end of the text |
(this|that) | either option |
( ) | a capture group: the part REGEXEXTRACT returns |
\. | a real dot (a bare . means any character) |
Capture groups are the trick. =REGEXEXTRACT(A2, "utm_campaign=([^&]+)") matches the whole utm_campaign=fall_boots but returns only the part in brackets: fall_boots. Read [^&]+ as "one or more characters that aren't &", so it stops at the next URL parameter. With two groups, REGEXEXTRACT spills both into side-by-side cells. In REGEXREPLACE, $1 puts the first group back: =REGEXREPLACE("8165550142", "(\d{3})(\d{3})(\d{4})", "($1) $2-$3") gives (816) 555-0142.
REGEXMATCH is the filter's best friend: =FILTER(A2:A500, REGEXMATCH(A2:A500, "^(how|what|why) ")) pulls every question-style search term out of a search terms report.
To run REGEXEXTRACT or REGEXREPLACE down a whole column, wrap it in ARRAYFORMULA, or hand each cell to it with MAP: =MAP(A2:A7, LAMBDA(url, REGEXEXTRACT(url, "utm_campaign=([^&]+)"))). MAP makes it easy to add IFERROR per row and to read later.
Gotchas:
- No match is an error. REGEXEXTRACT returns #N/A when the pattern isn't there. Wrap it in IFERROR with a label like "(none)".
- Text only. The regex functions refuse numbers.
TO_TEXT(A2)converts first. - Case counts.
"how"won't match "How". Start the pattern with(?i)to ignore case, or run LOWER on the text first. - It's RE2. Google's flavor skips a few features of other regex engines, like lookaheads. If a pattern from the internet errors, that's usually why.