Google Sheets is a powerful tool for organising and analysing data. While its basic functionalities are well-known, more advanced users often turn to regular expressions, or “regex,” to perform complex text manipulation tasks. In this article, we’ll dive deep into how you can harness the power of regex in Google Sheets to streamline your workflow.
What is Regex?
Regex, short for “regular expressions,” is a sequence of characters that forms a search pattern. It is often used to find or match text in a string, allowing for advanced text searching, extraction, and manipulation. Regular expressions can be simple, like matching a specific word, or complex, like matching email addresses, phone numbers, or patterns within data.
Why Use Regex in Google Sheets?
Using regex in Google Sheets allows you to:
- Search and Replace: Find specific patterns in your data and replace them with something else.
- Validate Data: Ensure that the data follows a specific format, such as phone numbers or email addresses.
- Extract Data: Pull specific information from within a cell, like extracting a domain from an email address.
- Clean Data: Remove unwanted characters, spaces, or formatting from your data.
How to Use Regex in Google Sheets
Google Sheets provides built-in functions that support regex. Here are the key functions:
1. REGEXMATCH
This function checks whether a text string matches a regular expression. It returns TRUE if there’s a match and FALSE otherwise.
Syntax:
=REGEXMATCH(text, regular_expression)Example: To check if cell A1 contains the word “Google”:
=REGEXMATCH(A1, "Google")2. REGEXEXTRACT
This function extracts a portion of a text string that matches a regular expression.
Syntax:
=REGEXEXTRACT(text, regular_expression)Example: To extract the domain from an email address in cell A1:
=REGEXEXTRACT(A1, "@(.+)")This regex looks for a pattern that starts with an “@” symbol followed by any characters. The (.+) captures all characters after the “@”.
3. REGEXREPLACE
This function searches for a pattern in a text string and replaces it with a different string.
Syntax:
=REGEXREPLACE(text, regular_expression, replacement)Example: To replace all numbers in cell A1 with an empty string (effectively removing them):
=REGEXREPLACE(A1, "[0-9]", "")The regex [0-9] matches any digit.
Common Regex Patterns
To effectively use regex, it’s helpful to understand some common patterns:
| Metacharacters | Description |
| . | Matches any single character except a newline. |
| * | Matches 0 or more of the preceding element. |
| + | Matches 1 or more of the preceding element. |
| ? | Makes the preceding element optional (matches 0 or 1 time). |
| \d | Matches any digit (equivalent to [0-9]). |
| \w | Matches any word character (alphanumeric plus underscore). |
| \s | Matches any whitespace character (spaces, tabs). |
| ^ | Matches the beginning of a string. |
| $ | Matches the end of a string. |
| [] | Matches any one of the characters inside the square brackets. |
| | | Acts as a logical OR within the regex |
Example: Extracting Specific Text
Suppose you have a column of email addresses, and you want to extract the usernames (the part before the @ symbol). You can use:
=REGEXEXTRACT(A1, "^[^@]+")This regex breaks down as:
^– Start of the string.[^@]– Any character except@.+– One or more of the previous character class.
Example: Validating Phone Numbers
To ensure that a phone number follows the pattern (123) 456-7890, you can use:
=REGEXMATCH(A1, "^\(\d{3}\) \d{3}-\d{4}$")This regex breaks down as:
^\(\d{3}\)– Matches exactly three digits enclosed in parentheses at the start of the string.\d{3}-\d{4}$– Matches three digits followed by a hyphen and four digits at the end of the string.
Tips for Using Regex in Google Sheets
- Test Your Regex: Before applying your regex to a large dataset, test it on a small sample to ensure it works as expected.
- Escape Special Characters: If you need to match a character that is also a special regex symbol, such as
.or*, you’ll need to escape it using a backslash (\). - Leverage Online Tools: Tools like regexr.com allow you to test and debug your regular expressions interactively.
Regex is a powerful tool within Google Sheets that can save you hours of manual work by automating complex text manipulation tasks. Whether you’re cleaning data, validating inputs, or extracting specific information, mastering regex will enhance your ability to work efficiently in Google Sheets. With the basics covered in this guide, you’re now equipped to start using regex in your own spreadsheets. Happy data processing!