If you’ve ever wished for a magic trick to search, extract, or clean up data in Google Sheets, you’re going to love learning about regular expressions — also called regex. Using just three functions — REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE — you can handle tricky text patterns with ease. Even if you’re new to spreadsheets, I’ll break it down in a way that feels like you’re chatting with a friend over coffee!
What Are REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE?
In simple words, these functions help you:
- REGEXMATCH: Check if a text matches a pattern (true/false).
- REGEXEXTRACT: Pull out part of a text based on a pattern.
- REGEXREPLACE: Find and replace parts of a text using a pattern.
Regular expressions might sound fancy, but think of them as smart search tools that recognize patterns like emails, phone numbers, dates, and more.
Real-Life Example: Cleaning a List of Phone Numbers
Suppose you have messy phone numbers coming from a form where people type differently:
| Phone Numbers |
|---|
| (123) 456-7890 |
| 123.456.7890 |
| 123-456-7890 |
| +1 123 456 7890 |
You want to clean them all into the format: 1234567890. This is where regex magic happens!
Step-by-Step Instructions
1. Use REGEXMATCH to Check Format
Formula:
=REGEXMATCH(A2, "^\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}$")
- This checks if the number looks like a phone number.
TRUEmeans it matches,FALSEmeans it doesn’t.
2. Use REGEXEXTRACT to Pull Specific Parts
Formula:
=REGEXEXTRACT(A2, "\d{3}")
- This extracts the first three digits (area code) from the phone number.
3. Use REGEXREPLACE to Clean the Numbers
Formula:
=REGEXREPLACE(A2, "[^\d]", "")
- This removes anything that is not a digit — so parentheses, dashes, dots, spaces, etc., all disappear!
Sample Output Table
| Original Phone Number | Cleaned Number |
|---|---|
| (123) 456-7890 | 1234567890 |
| 123.456.7890 | 1234567890 |
| 123-456-7890 | 1234567890 |
| +1 123 456 7890 | 11234567890 |
Key Benefits of Using REGEX Functions
- Save Hours: Clean or organize messy text data in seconds.
- Find Hidden Patterns: Search complex text without manually reading each line.
- Automate Tedious Tasks: Transform raw data into useful information easily.
- Powerful Yet Simple: A few lines of formula can do what would take hours manually.
Pro Tips for Beginners
- Start Simple: Begin with small patterns like digits (
\d) or letters (\w). - Use Online Testers: Websites like regex101.com help you test patterns before using them in Sheets.
- Remember Special Characters: Characters like
.,(,)have special meanings in regex, so you might need to “escape” them with a backslash (\.).
Quick-Reference Cheat Sheet
| Task | Formula |
|---|---|
| Check if text matches a pattern | =REGEXMATCH(A2, “pattern”) |
| Extract part of a text | =REGEXEXTRACT(A2, “pattern”) |
| Replace text matching a pattern | =REGEXREPLACE(A2, “pattern”, “replacement”) |
| Remove all non-digits | =REGEXREPLACE(A2, “[^\d]”, “”) |
Learning to use REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE opens up a world of possibilities in Google Sheets. Even simple patterns can help you clean, organize, and analyze your data like a pro. Start experimenting with small examples, and soon you’ll feel like a spreadsheet wizard!