Regex & Automation
REGEXEXTRACT and REGEXREPLACE: Clean Messy Workplace Data Fast
skills.efektifpro.com – Data exported from CRM platforms, legacy accounting tools, and customer web forms is notoriously inconsistent. Analysts frequently waste hours performing manual text-to-columns splits and repetitive find-and-replace routines. Regular Expressions (Regex) provide a mathematical approach to pattern matching, allowing you to parse complex strings with razor-sharp precision in Google Sheets.
⚡ The Three Core Spreadsheet Regex Functions
=REGEXEXTRACT(text, pattern): Pulls matching substrings out of text (e.g., extracting an Order ID).=REGEXREPLACE(text, pattern, replacement): Swaps pattern matches with new text (e.g., stripping non-numeric characters).=REGEXMATCH(text, pattern): ReturnsTRUEorFALSEif a pattern exists (ideal for conditional formatting & filtering).
Essential Regex Token Cheat Sheet
| Token | Meaning | Example Match |
|---|---|---|
\d |
Any single digit (0–9) | \d{4} matches 2026 |
\w |
Word character (letters, numbers, underscore) | \w+ matches SKU_902 |
[a-zA-Z] |
Any alphabetic letter | Matches any uppercase/lowercase letter |
+ |
One or more occurrences | \d+ matches 4500 |
() |
Capturing group (specifies what to extract) | @([\w\.-]+) captures domain after @ |
[^0-9] |
Negated set (anything that is NOT a digit) | Used in cleaning phone numbers |
Practical Workplace Recipes
Recipe 1: Extract Clean Domain from Chaotic Email Addresses
Suppose cell A2 contains raw contact strings like "Contact: sarah.jenkins@acmecorp.com (Billing)". To extract only the domain name (acmecorp.com):
=REGEXEXTRACT(A2, "@([a-zA-Z0-9\.-]+)")Recipe 2: Sanitize Dirty Phone Numbers into Standard Digits
If raw customer input in cell A2 contains varied formatting like "+1 (555) 329-8472 ext 102", strip all non-numeric characters instantly:
=REGEXREPLACE(A2, "\D", "")Result: 15553298472102. The \D token targets every character that is NOT a digit and deletes it completely.
Recipe 3: Extract Invoice Number Embedded in Freeform Descriptions
When transaction descriptions read "Wire transfer received for INV#84920 on Friday", isolate the invoice digits cleanly:
=REGEXEXTRACT(A2, "INV#(\d+)")Result: 84920. The parentheses create a capturing group, instructing Sheets to locate the prefix INV# but return only the enclosed digits!