Using Regular Expressions in Spreadsheets for Email List Cleaning: REGEXMATCH, REGEXEXTRACT and REGEXREPLACE in Google Sheets and Power Query in Excel
By Email ExtractorPublished 5 min read
On this page
Why Regex in Spreadsheets
Regular expressions (regex) let you search, validate, extract and transform text patterns inside spreadsheet formulas. For email list work, regex handles tasks that standard text functions (LEFT, RIGHT, MID, FIND, SUBSTITUTE) cannot:
Task
Standard functions
Regex approach
Validate email format
Complex nested IF/FIND/LEN combinations that miss edge cases
Single REGEXMATCH with email pattern
Extract email from mixed text
Multiple nested MID/FIND formulas that break on varying positions
Single REGEXEXTRACT that finds the email anywhere
Remove extra spaces and characters
Multiple nested SUBSTITUTE calls
Single REGEXREPLACE
Find domain from email
RIGHT + LEN - FIND("@")
REGEXEXTRACT with capture group after @
Identify role-based addresses
Multiple OR(SEARCH("info@"),...) combinations
REGEXMATCH with alternation pattern
Standardise formatting
Chain of LOWER + TRIM + SUBSTITUTE
REGEXREPLACE handles all in fewer steps
Google Sheets Regex Functions
Google Sheets has three built-in regex functions. All are case-sensitive by default:
Power Query (Get and Transform Data) supports text pattern matching. While M-language does not have full regex, it handles common email cleaning tasks:
Task
M-language approach
Example
Trim whitespace
Text.Trim([Email])
Removes leading and trailing spaces
Lowercase
Text.Lower([Email])
Standardises case
Contains @ check
Text.Contains([Email], "@")
Basic validation
Extract domain
Text.AfterDelimiter([Email], "@")
Gets domain from email
Extract local part
Text.BeforeDelimiter([Email], "@")
Gets local part from email
Replace characters
Text.Replace([Email], "<", "")
Removes specific characters
LAMBDA and LET functions (Excel 365)
Excel 365 users can create custom regex-like functions using LAMBDA:
Spreadsheet regex works well for cleaning and validating small lists (hundreds to a few thousand emails). For larger tasks, Email Extractor handles extraction and deduplication more efficiently:
Task
Spreadsheet regex
Email Extractor
Extract emails from a few cells of text
Good: REGEXEXTRACT formula
Not needed for small amounts
Validate format of a short list
Good: REGEXMATCH column
Not needed for small lists
Extract emails from large files (CSV, XLSX, PDF, DOCX, HTML)
Slow and complex for multiple files
Upload files directly; automatic extraction
Deduplicate across multiple sources
Manual: COUNTIF + remove duplicates
Automatic case-insensitive deduplication
Extract from file types spreadsheets cannot open (PDF, DOCX, EML, MSG, VCF)
Cannot do
Supports 19 file types
Process multiple files at once
One at a time
Batch processing up to 100 MB
For best results, use Email Extractor for bulk extraction and deduplication, then use spreadsheet regex formulas for additional cleaning, validation, segmentation and enrichment of the extracted list.
