Validate email shape
REGEXMATCH(A2, "^[\\w.-]+@[\\w.-]+\\.[a-zA-Z]{2,}$") flags rows to fix before a mail merge.
How does regex work in Google Sheets?
Regular expressions (regex) describe text patterns. Google Sheets exposes them through REGEXMATCH (test), REGEXEXTRACT (pull a substring), and REGEXREPLACE (swap text). They shine when TRIM and SPLIT are not enough for inconsistent labels, URLs, or log lines.
When to use it
Use regex when you need to test format (email-like strings), extract order IDs from notes, pull domains from URLs, or clean merged export fields without manual find-and-replace.
When to skip it
Skip regex for simple equals checks or fixed-width splits. Avoid nested regex on huge columns if a helper table or Apps Script with clear steps is easier for teammates to maintain.
How it works
-
1
{:"Pick the function"=>"REGEXMATCH for true/false, REGEXEXTRACT for first capture, REGEXREPLACE to rewrite."}
-
2
Write a pattern in quotes. Google uses RE2 syntax; backslashes escape special characters like . and ?.
-
3
Use capturing groups (... ) in REGEXEXTRACT to return the part you need from a longer string.
-
4
Combine with IF, ARRAYFORMULA, or FILTER so one formula covers a column without dragging forever.
-
5
Test the pattern on five messy real rows before filling down ten thousand cells.
-
6
Document the pattern in a comment row or Notes column so the next editor knows what it matches.
Examples in Google Sheets
Extract invoice number
REGEXEXTRACT(B2, "INV-(\\d+)") pulls digits from strings like "Paid INV-10492 via wire".
Normalize phone digits
REGEXREPLACE(C2, "[^0-9]", "") strips punctuation so VLOOKUP keys align across systems.
Build this without starting from a blank cell
Use a Better Sheets tool for regex in Google Sheets, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Forgetting REGEXEXTRACT returns only the first match when multiple exist in one cell.
- Using greedy .* on huge strings and getting surprising captures; tighten the pattern instead.
- Expecting case-insensitive match without (?i) or LOWER wrappers where needed.
- Nesting too many regex formulas instead of one helper column per extraction step.
- Copying PCRE patterns from Stack Overflow that RE2 does not support.
Frequently asked questions
- Which regex functions exist in Sheets?
- REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE cover most spreadsheet text tasks.
- Is regex case sensitive?
- By default, yes. Wrap with LOWER() on both sides or use inline flags supported by RE2 where applicable.
- REGEXEXTRACT vs SPLIT?
- SPLIT works on fixed delimiters. REGEXEXTRACT handles variable text between markers.
- Can ARRAYFORMULA use regex?
- Yes. ARRAYFORMULA(REGEXEXTRACT(A2:A, "pattern")) fills a column in one formula.
- Why
- Invalid pattern syntax or no match when REGEXEXTRACT requires a match. Test with REGEXMATCH first.
- Match whole word only?
- Use word boundaries in RE2, such as \\bword\\b inside your pattern string.
- Replace with capture groups?
- REGEXREPLACE supports $1, $2 for captured groups in the replacement text.
- Regex vs Apps Script?
- Formulas are fine for column transforms. Script helps on multi-step files or very large batch jobs.
Related Tutorials
Watch how regex in Google Sheets works
200 REGEXMATCH Examples in Google Sheets
REGEXMATCH Formula Generator for Google Sheets
Related blog posts
Guides that explain regex in Google Sheets in more depth.
Philippines Phone Number Validation: Use of REGEXMATCH in Google Sheets
Every school year thousands of students and teachers’ data were being added to the school database. As someone who was always given the task to narrow down lots of information after the school enrollment, validating students’ data manually is very exhausting. Take this as an example, when you ask students to include the phone numbers of their emergency contact person, there are some instances wherein the declared numbers were invalid due to human error. And the assigned personnel during enr...
Read post →Extract URLs from Google Sheets
Extract url from hyperlink google sheets.Skip the Command K. This Google Sheet tutorial will make it easier for you to extract URLS from Google Sheets.
Read post →Related glossary terms
Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →normalizing data in Sheets
Normalizing means making inconsistent inputs follow one reliable shape: same date format, trimmed text, unique keys, one fact per row, and separate columns for categories that were crammed into one field. Clean data makes VLOOKUP, QUERY, and dashboards dependable.
Read guide →SQL-style queries (QUERY)
The QUERY function runs a SQL-like statement against a range in your sheet. You SELECT columns, filter with WHERE, sort with ORDER BY, and aggregate with GROUP BY without building many helper columns. It is the closest native option to a small in-sheet database query.
Read guide →visual formulas in Sheets
Visual formulas are spreadsheet layouts where the formula output is meant to be seen, not just calculated: sparkline strips, emoji status icons, color scales, and REPT bars that communicate trend and health at a glance. They sit between raw numbers and full chart objects for dense dashboards.
Read guide →Done reading about regex in Google Sheets?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.