Regex in Google Sheets: Patterns, Extracts, and Replacements
Master regex in Google Sheets using REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE to validate, extract, and transform text across datasets. Learn practical patterns, optimize performance, and scale with ARRAYFORMULA for large sheets.
Regex in Google Sheets enables pattern-based text operations using functions like REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE. It helps validate data, pull subpatterns, and transform text with concise rules. For example, REGEXMATCH(A2, "^[A-Za-z]+[0-9]*$") checks letters followed by digits, while REGEXEXTRACT(A2, "([^.]+)") pulls the first segment. Combine patterns with IF, FILTER, and ARRAYFORMULA for scalable workflows.
What regex in Google Sheets is and why it matters
Regex in Google Sheets opens a toolbox for text processing. Instead of writing long string-manipulation formulas, you can express complex rules with compact patterns. The key functions are REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE, each serving a distinct purpose: validation, extraction, and substitution. This makes data cleaning, validation, and transformation faster and more robust, especially when dealing with inconsistent input formats.
According to How To Sheets, regex-powered workflows reduce manual parsing time by enabling rule-based checks across columns and rows. In practice, you combine these regex primitives with standard Sheet operations like IF, FILTER, and ARRAYFORMULA to scale from a single cell to entire datasets.
=REGEXMATCH(A2, "^[A-Za-z]+[0-9]*$")This simple pattern matches a string of letters followed by optional digits. The result is a boolean you can feed into IF to branch logic, or into FILTER to restrict rows. Using anchored patterns and character classes keeps your formulas readable and portable across sheets.
- Anchor basics: ^ matches start, $ matches end, . matches any character except newline.
- Character classes: [0-9] for digits, [A-Za-z] for letters, and [^...] for negated sets.
Next, we’ll explore practical examples that show how to apply these ideas to everyday data tasks.
Basic patterns and anchors
Patterns with anchors let you match positions in strings. The caret ^ anchors the start, the dollar sign $ anchors the end, and [.] or [.]+ helps define literals. The following examples show how to test simple prefixes and extract subpatterns without backreferences.
=REGEXMATCH(A2, "^Hello")=REGEXMATCH(A2, "^[A-Za-z]+$")These formulas return TRUE when the text starts with a given prefix or contains only letters. A common pattern for splitting emails is to pull the user name before @, or the domain after @, using capturing groups:
=REGEXEXTRACT(A2, "^([^@]+)")=REGEXEXTRACT(A2, "@([^.]+)")Validation with REGEXMATCH
Regex-based validation helps keep data clean by rejecting malformed entries. A classic use is email validation. The following formula checks a basic email structure and returns TRUE for matches. You can combine it with IF to flag invalid rows. For phone numbers, adjust the pattern to reflect your locale.
=REGEXMATCH(B2, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,}$")=IF(REGEXMATCH(C2, "^[0-9]{3}-[0-9]{3}-[0-9]{4}$"), "OK", "Invalid")Extracting with REGEXEXTRACT
REGEXEXTRACT pulls a portion of text that matches a pattern. You can capture a username, domain, or date component. Use parentheses to define capture groups and refer to them with the function output. If no match exists, REGEXEXTRACT returns an error, so pair with IFERROR or check LEN().
=REGEXEXTRACT(A2, "^([^@]+)")=REGEXEXTRACT(A2, "@([^.]+)")Replacing with REGEXREPLACE
REGEXREPLACE transforms text by substituting matched patterns with a replacement string. This is powerful for masking, canonicalizing formats, or reformatting values in a single pass. Remember to escape literals only when necessary. The example below masks a date-like pattern and hides the middle digits of a phone number.
=REGEXREPLACE(A2, "([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+)", "$1 at $2")=REGEXREPLACE(A2, "([0-9]{3})-([0-9]{2})-([0-9]{4})", "***-**-****")Performance and best practices
Regex is powerful but can be heavy on large datasets. Avoid multiple nested regex formulas in a single row; instead, compute once per row and reuse the result. When applying to many rows, prefer ARRAYFORMULA to process entire columns rather than copying formulas. Always test on a subset of data before scaling up to thousands of rows.
=ARRAYFORMULA(IF(A2:A="",, REGEXEXTRACT(A2:A, "@([^.]+)")))=ARRAYFORMULA(REGEXMATCH(A2:A, "^[A-Za-z]+[0-9]*$"))End-to-end pattern example
Here is a practical end-to-end example that cleans a list of emails by extracting the username and domain, then producing a readable replacement. Adjust the patterns to match your data. Start by creating a few test rows, then extend to the entire column with ARRAYFORMULA.
// Extract username
=REGEXEXTRACT(A2, "^([^@]+)")
// Extract domain
=REGEXEXTRACT(A2, "@([^.]+)")
// Recombine as: username (domain)
=REGEXREPLACE(A2, "^([^@]+)@([^.]+)$", "$1 ($2)")Common pitfalls and debugging tips
Regex in Sheets can fail for regex engines differences; Keep in mind: no lookahead/lookbehind support; test patterns in small samples; Use IFERROR to handle errors; Use character classes to avoid escaping too much; Use quotes correctly in the string by using double escaping in JSON; According to How To Sheets, start with simple patterns and scale up.
=IFERROR(REGEXEXTRACT(A2, "^([^@]+)"), "not found")=ARRAYFORMULA(IF(A2:A="", "", REGEXEXTRACT(A2:A, "@([^.]+)")))Quick-start templates you can copy today
A compact template that extracts domains and validates emails across a column:
=ARRAYFORMULA(IF(A2:A="", "", REGEXEXTRACT(A2:A, "@([^.]+)")))=ARRAYFORMULA(IF(A2:A="", "", REGEXMATCH(A2:A, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,}$")))Steps
Estimated time: 45-75 minutes
- 1
Define the task
Clarify the regex pattern you need (validation, extraction, or replacement) and identify sample data to test against. Write down the exact test cases you will use.
Tip: Start with a simple pattern and expand as needed. - 2
Create a test dataset
Populate a few rows with typical inputs and a few edge cases to ensure robustness of your regex.
Tip: Include both valid and invalid examples to verify behavior. - 3
Build core formulas
Implement REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE in separate helpers to isolate concerns.
Tip: Use parentheses to define capture groups for REGEXEXTRACT. - 4
Scale with ARRAYFORMULA
Wrap formulas with ARRAYFORMULA to apply across whole columns instead of copying down.
Tip: Test on a smaller range before applying to the entire dataset. - 5
Validate and document
Cross-check results against test cases, add error-handling with IFERROR, and document patterns used for future maintenance.
Tip: Maintain a small reference sheet with your patterns.
Prerequisites
Required
- Required
- Basic familiarity with Google Sheets formulasRequired
Optional
- Test dataset for regex experimentsOptional
- Optional: Internet access for online referencesOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy values or formulas from cells | Ctrl+C |
| PastePaste into cells or formulas | Ctrl+V |
| FindSearch within the sheet | Ctrl+F |
| Fill downApply formula downward | Ctrl+D |
| Fill rightApply formula to the right | Ctrl+R |
FAQ
What is the difference between REGEXMATCH and REGEXEXTRACT?
REGEXMATCH returns a boolean indicating whether a text matches a pattern, while REGEXEXTRACT returns the actual substring that matches a capturing group. Use REGEXMATCH for validation and REGEXEXTRACT when you need a specific portion of text.
REGEXMATCH tells you yes or no, REGEXEXTRACT gives you the matched text part.
Do Google Sheets regex functions support flags like i for case-insensitive?
Google Sheets regex uses the RE2 engine and does not support inline flags in the same way as some languages. Case sensitivity is the default behavior; for case-insensitive matches, normalize case in your data or use explicit character classes.
Sheets regex follows RE2 rules and doesn’t support typical regex flags like in other languages.
Can I apply regex to an entire column in Sheets?
Yes. Use ARRAYFORMULA to apply REGEXMATCH, REGEXEXTRACT, or REGEXREPLACE across a whole column, typically starting from row 2 to accommodate headers.
Absolutely—use ARRAYFORMULA to cover the whole column at once.
How do I escape special characters in Sheets regex patterns?
Most literals like dot, parentheses, or plus should be matched literally by placing them inside a character class or escaping with backslashes. Prefer [.] to represent a literal dot to avoid backslashes in JSON.
Escape carefully; use character classes when possible to avoid backslashes.
Are there performance considerations when using regex on large datasets?
Yes. Regex can be slow on very large datasets. Prefer pre-filtering with simple checks, reuse intermediate results, and apply formulas with ARRAYFORMULA to minimize repeated calculations.
Be mindful of performance; test on smaller samples before scaling up.
The Essentials
- Master REGEXMATCH, REGEXEXTRACT, REGEXREPLACE in Sheets
- Anchor patterns with ^ and $ for precision
- Combine with ARRAYFORMULA for column-wide results
- Handle errors with IFERROR and LEN checks
- Test patterns on realistic samples before scaling