# REGEXMATCH, REGEXEXTRACT and REGEXREPLACE (Google Sheets lesson 11 of 17)

> Find, pull out and rewrite text by pattern instead of by position.

Source: https://spreadsheetpractice.com/google-sheets/learn/regexmatch-regexextract-regexreplace · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets power tools"
- **Time:** about 12 minutes, 3 graded exercises
- **Functions:** `REGEXMATCH`, `REGEXEXTRACT`, `REGEXREPLACE`, `TO_TEXT`, `FILTER`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=REGEXMATCH(A2,"(?i)refund")`: TRUE if "refund" appears, in any case

## 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

| Formula | What it does |
|---|---|
| `=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 matches |

## Watch 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

Solve them in the live grid at [REGEXMATCH, REGEXEXTRACT and REGEXREPLACE](https://spreadsheetpractice.com/google-sheets/learn/regexmatch-regexextract-regexreplace). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **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.
2. **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.
3. **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.

Previous: [SPLIT, JOIN and text cleanup](https://spreadsheetpractice.com/google-sheets/learn/split-join-text) · Next: [QUERY basics: select, where, order by](https://spreadsheetpractice.com/google-sheets/learn/query-function-basics)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
