Google Sheets lesson 8 of 11, Sheets power tools
REGEXMATCH, REGEXEXTRACT and REGEXREPLACE
Find, pull out and rewrite text by pattern instead of by position.
Watch it done
5 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
A regular expression is a short pattern that describes text. \d means any digit, + means one or more of the thing before it, ^ and $ mean the start and end of the text, and . means any character. So ORD-\d+ matches "ORD-" followed by digits. Google Sheets has three functions that use them, and Excel has no exact match for this trio.
REGEXMATCH returns TRUE when the pattern appears anywhere in the text. Matching is case-sensitive, so start the pattern with (?i) to ignore case: =REGEXMATCH(A2, "(?i)refund") matches "Refund" and "REFUND" too.
REGEXEXTRACT returns the first piece of text that matches. Put part of the pattern in parentheses to return only that part: =REGEXEXTRACT(B2, "@(.+)") returns everything after the @. When nothing matches, it returns #N/A, so wrap it in IFNA when some rows won't have a match. REGEXREPLACE replaces every match: =REGEXREPLACE(B2, "^[^@]+", "***") hides the part of an email before the @.
These functions expect text. Give REGEXMATCH a number, such as a zip code typed as 97205, and it returns an error. Convert it first with TO_TEXT. REGEXMATCH also works on a whole range inside FILTER: =FILTER(B2:B7, REGEXMATCH(A2:A7, "(?i)refund")). Sheets uses the RE2 flavor of regular expressions, which leaves out a few features from other tools, such as lookaheads.
Syntax
=REGEXMATCH(A2,"(?i)refund")TRUE if "refund" appears, in any case=REGEXEXTRACT(A2,"ORD-\d+")The first order number in the text=REGEXEXTRACT(B2,"@(.+)")Only the part in parentheses=REGEXREPLACE(B2,"^[^@]+","***")Replace the text before the @=REGEXMATCH(TO_TEXT(C2),"^97")Test a number as text=FILTER(B2:B7,REGEXMATCH(A2:A7,"refund"))Rows whose text matchesWatch out for
- Matching is case-sensitive. "refund" won't match "Refund" unless the pattern starts with (?i).
- REGEXEXTRACT returns #N/A when there is no match. Wrap it in IFNA for rows that may not have one.
- A number in the text argument returns an error. Use TO_TEXT first.
- A dot means any character. To match a real dot, as in ".com", write \. instead.
The exercises
- Flag refunds and Oregon zips. In D2, show TRUE when the message in A2 mentions a refund, in any capitalization. In E2, show TRUE when the zip code in C2 starts with 97. Fill both down to row 7.
- Order numbers and domains. In F2, pull out the order number, such as ORD-48213, from the message in A2. Show "No order" when the message has none. In G2, pull out the part of the email in B2 after the @. Fill both down to row 7.
- Mask emails, list refund requests. In H2, replace everything before the @ in B2 with "***", and fill down to H7. In J2, use FILTER with REGEXMATCH to list the emails from B2:B7 whose message in A2:A7 mentions a refund, in any capitalization.
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.
- Flag refunds and Oregon zips
- Order numbers and domains
- Mask emails, list refund requests