Standard text functions like LEFT, RIGHT, MID, and SUBSTITUTE work well for simple edits, but they quickly break down when dealing with messy strings, variable-length codes, or inconsistent data exports.
To manipulate text with complete flexibility, Google Sheets provides three built-in Regular Expression (Regex) functions: REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE. In this tutorial, you'll learn how to validate, extract, and clean text strings using practical, copy-paste formulas.
Quick Regex Pattern Cheat Sheet
Before diving into the formulas, here are the core regex symbols you will use most often:
\d– Matches any single digit (0–9).[a-zA-Z]– Matches any letter.+– Matches one or more of the preceding character..*– Matches any sequence of characters.^– Marks the beginning of a string.$– Marks the end of a string.|– Acts as an OR operator.
1. REGEXMATCH (Validate & Check Patterns)
Syntax: =REGEXMATCH(text, regular_expression)
REGEXMATCH tests whether a text cell contains a specific pattern and returns TRUE or FALSE. It is commonly used inside IF() statements or Conditional Formatting rules.
Example A: Check If a Cell Contains a Valid Email Address
Example B: Flag Any Text Containing Specific Keywords (OR Condition)
To check if a customer feedback comment mentions "delay", "broken", or "refund" (case-insensitive flag (?i)):
2. REGEXEXTRACT (Pull Specific Data Out of Strings)
Syntax: =REGEXEXTRACT(text, regular_expression)
REGEXEXTRACT pulls out substrings that match your pattern, making it ideal for parsing messy transaction logs and addresses.
Example A: Extract Only Numbers from a Mixed SKU (e.g., "INV-8942-NY")
Example B: Extract the Domain Name from a Full URL
Given https://www.example.com/blog/article in cell A2, extract just the domain:
Example C: Extract Text Inside Parentheses (e.g., "John Doe (Marketing)")
3. REGEXREPLACE (Find & Replace Complex Patterns)
Syntax: =REGEXREPLACE(text, regular_expression, replacement)
Unlike standard SUBSTITUTE, REGEXREPLACE allows you to swap entire character classes in one command.
Example A: Strip All Non-Numeric Characters (Clean Phone Numbers)
Transform phone numbers like (555) 019-2834 or +1-555-019-2834 into clean digits 5550192834:
Example B: Remove Multiple Spaces and Replace with a Single Space
Using Regex with ARRAYFORMULA
All three regex functions can be combined with ARRAYFORMULA to process entire columns automatically from row 2 downwards:
Comments
Post a Comment