The formula
Extract an invoice IDGoogle Sheets
=REGEXEXTRACT(A2,"INV-[0-9]+")In C2 → INV-20931
Extract an email addressGoogle Sheets
=REGEXEXTRACT(A3,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]+")In C3 → ops@gosuoo.com
Test a pattern (case-insensitive)Google Sheets
=REGEXMATCH(A4,"(?i)refund")In C4 → TRUE
Replace digitsGoogle Sheets
=REGEXREPLACE(A2,"[0-9]","#")In C5 → Paid INV-##### by card
Excel 365 equivalentExcel 365
=REGEXEXTRACT(A2,"INV-[0-9]+")In C6 → INV-20931
Excel 365: test (case-insensitive)Excel 365
=REGEXTEST(A4,"refund",1)In C7 → TRUE
✓ Checked. 6 results for formulas tagged Excel 365 / Google Sheets were checked with a reference implementation of the documented behavior, because LibreOffice can't run those functions.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | |
|---|---|---|---|
| 1 | Text | ||
| 2 | Paid INV-20931 by card | INV-20931 | |
| 3 | Contact: ops@gosuoo.com | ops@gosuoo.com | |
| 4 | Refund requested | TRUE | |
| 5 | Paid INV-##### by card | ||
| 6 | INV-20931 | ||
| 7 | TRUE |
Regex results were checked with a reference regex engine (the patterns here use only syntax shared by RE2 and PCRE2). The Excel REGEX* syntax follows Microsoft's documentation.
How it works
- INV-[0-9]+
- Literal "INV-" then one or more digits.
- (?i)
- Sheets/RE2 flag for case-insensitive matching. In Excel use the case_sensitivity argument 1.
- \.
- A literal dot (an unescaped . means "any character").
- "#"
- REGEXREPLACE replacement text; all matches are replaced.
Common errors and fixes
Why does the formula return #N/A in Sheets?
REGEXEXTRACT found no match. Wrap in IFERROR(…,"").
Number returned as text?
REGEXEXTRACT always returns text. Wrap in VALUE() to do math.
Pattern works in Excel but not Sheets?
Sheets uses RE2, which doesn't support lookaheads like (?=…). Rewrite with a capture group.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.