Have you ever needed to pull specific text from a messy string, check if a pattern exists in a cell, or clean up a batch of data in Google Sheets? That’s where REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE come in. These functions use regular expressions (regex) to identify and manipulate text patterns—perfect for cleaning data, splitting info, or validating entries.
Even if you’re a beginner, don’t worry—this guide will walk you through how to use these powerful functions in a clear, friendly, and practical way. You’ll soon find yourself solving problems that would otherwise require hours of manual work.
What is REGEX and Why Use It in Google Sheets?
Regex, short for “regular expression,” is like a search language for text. It helps you:
- Identify whether a certain pattern exists in a string (e.g., check if a cell contains a phone number)
- Extract just the piece of information you need (like getting a domain from an email)
- Replace unwanted patterns with clean or standardized text
Google Sheets supports three regex functions to help with this:
- REGEXMATCH: Returns TRUE or FALSE if a pattern is found
- REGEXEXTRACT: Extracts the first instance of a matching pattern
- REGEXREPLACE: Replaces matching patterns with custom text
Real-Life Example: Cleaning Up Email List Data
Imagine you have a messy list of email-related data, and you want to:
- Verify which rows contain valid-looking email addresses
- Extract just the domain from each email
- Replace company names in the domain with a cleaner format
Sample Data Table
| A | Email Data |
|---|---|
| 1 | jane.doe@gmail.com |
| 2 | info@my-company.org |
| 3 | hello[at]fake-email.com |
Using REGEXMATCH to Validate Email Format
We want to check if column B contains a standard email format:
=REGEXMATCH(B2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")This returns TRUE if the email is valid-looking. For row 3, it will return FALSE due to the [at] format.
Using REGEXEXTRACT to Get the Email Domain
To extract just the domain (like gmail.com or my-company.org):
=REGEXEXTRACT(B2, "@(.+)$")This grabs everything after the “@” symbol. For jane.doe@gmail.com, the result is gmail.com.
Using REGEXREPLACE to Clean the Domain Name
Let’s say you want to clean company names in domains by removing dashes:
=REGEXREPLACE(REGEXEXTRACT(B2, "@(.+)$"), "-", "")This turns my-company.org into mycompany.org.
More Practical Use Cases
- Extract phone numbers:
=REGEXEXTRACT(A2, "\d{3}-\d{3}-\d{4}") - Find if a cell contains a specific word:
=REGEXMATCH(A2, "invoice") - Remove special characters:
=REGEXREPLACE(A2, "[^a-zA-Z0-9]", "")
Quick-Reference Summary: REGEX Functions Cheat Sheet
| Function | What It Does | Example |
|---|---|---|
| REGEXMATCH | Checks if a cell matches a pattern | =REGEXMATCH(A1, “invoice”) |
| REGEXEXTRACT | Pulls out the first match from a string | =REGEXEXTRACT(A1, “@(.+)$”) |
| REGEXREPLACE | Replaces text that matches a pattern | =REGEXREPLACE(A1, “-“, “”) |
REGEX functions might seem intimidating at first, but once you get the hang of them, they become incredibly useful for handling text in smart, automated ways. From cleaning up messy data to pulling out key details from strings, Google Sheets gives you all the tools you need to work like a pro—even if you’re just starting out.
Start small by experimenting with patterns, and use real-world scenarios like emails, phone numbers, and URLs to build your confidence. In no time, you’ll be mastering Google Sheets regex like a data-cleaning wizard.