SheetsdrillREGEXMATCH, REGEXEXTRACT and REGEXREPLACE
REGEXMATCH(text, regex) returns TRUE if the text contains the pattern, REGEXEXTRACT(text, regex) returns the first matching part (or the first capture group), and REGEXREPLACE(text, regex, replacement) replaces every match. Google Sheets uses RE2 syntax, so patterns like \d+ for digits and [A-Z]{3} for three capital letters work, but lookaheads do not.
Syntax
=REGEXMATCH(text, regular_expression), =REGEXEXTRACT(text, regular_expression), =REGEXREPLACE(text, regular_expression, replacement)| Part | What it is |
|---|---|
text | The cell or string to test. It must be text; wrap numbers in TO_TEXT. |
regular_expression | The pattern in double quotes, RE2 syntax. Escape special characters with a backslash, for example \. for a literal dot. |
replacement | REGEXREPLACE only: the new text; $1 inserts the first capture group. |
Examples
=REGEXMATCH("Order INV-2041 shipped", "INV-\d+")Result: TRUE
=REGEXEXTRACT("Order INV-2041 shipped", "INV-(\d+)")Result: 2041
The parentheses return only the number; without them you get INV-2041. The result is text.
=REGEXREPLACE("(555) 123-4567", "[^0-9]", "")Result: 5551234567
Removes everything that is not a digit.
Common mistakes
- #VALUE! Function REGEXMATCH parameter 1 expects text values means the cell holds a number or date; use TO_TEXT(A1) or A1&"".
- REGEXEXTRACT returns #N/A when nothing matches; wrap it in IFERROR to show a blank.
- A dot matches any character, so "gmail.com" also matches gmailXcom; write "gmail\.com".
Practice REGEXMATCH, REGEXEXTRACT and REGEXREPLACE
These drills check your answer on live data, free, no account needed.
- Valid order IDsA valid order ID is exactly "ORD-" followed by four digits, in capitals. Count the valid IDs in column A with REGEXMATCH inside ARRAYFORMULA. Answer in D1.
- Pull out the invoice numberExtract the invoice number from each payment note as a real number, so it can be sorted and matched. Fill B2 and B3 using REGEXEXTRACT.
- Digits onlyPhone numbers were typed in different styles. Remove everything that is not a digit, keeping the result as text. Fill B2 and B3 using REGEXREPLACE.
Questions
How do I extract an email domain?
=REGEXEXTRACT(A1, "@(.+)$") returns everything after the @ sign.
How do I extract all numbers from a cell?
=REGEXREPLACE(A1, "[^0-9]", "") keeps only the digits as text; wrap in VALUE to get a number. REGEXEXTRACT alone returns only the first match.
Related
- SPLITHow SPLIT in Google Sheets breaks text into separate cells by a comma, space or other delimiter, how to keep only the first or last piece, and common errors.
- SUBSTITUTEHow SUBSTITUTE in Google Sheets replaces one piece of text with another, removes characters, targets one occurrence, and how it differs from REGEXREPLACE.
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
- LEFT, RIGHT and MIDHow LEFT, RIGHT and MID in Google Sheets extract characters from the start, end or middle of a cell, with FIND for variable positions, and common errors.