How to Clean Messy Spreadsheet Data with Google Sheets Regex
Use Google Sheets regex functions to create a separate cleaned or review column while keeping the original data unchanged. REGEXEXTRACT returns matching text, REGEXREPLACE transforms text, and REGEXMATCH flags whether a pattern exists. Test a representative sample before filling a formula down a production sheet.
Choose the function that matches the job
| Function | Use it for | Common mistake |
|---|---|---|
REGEXEXTRACT | Return the first text matching a pattern | Assuming it returns every match |
REGEXREPLACE | Remove or rewrite matching text | Overwriting the only copy of the source |
REGEXMATCH | Flag rows for validation or review | Treating a match as proof that the whole value is valid |
Extract a simple email-like value
=IFERROR(REGEXEXTRACT(A2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"),"")This is useful for finding a likely email-shaped substring in ordinary text. It is not a complete validation of every address allowed by email standards and does not prove the mailbox exists.
Normalize phone text without inventing a country format
=REGEXREPLACE(A2,"[^0-9+]","")The formula removes spaces, parentheses, and hyphens while retaining digits and plus signs. It does not determine country code, national trunk prefix, extension, or whether the number is valid. Keep the original and use region-specific validation when the number has business consequences.
Flag values that require manual review
=IF(REGEXMATCH(A2,"^[A-Z]{3}-[0-9]{5}$"),"OK","REVIEW")This example expects three uppercase letters, a hyphen, and five digits. Anchors ^ and $ matter because without them a valid-looking substring inside a longer invalid value can still match.
Use capture groups deliberately
=REGEXEXTRACT(A2,"^([A-Z]{3})-([0-9]{5})$")Capture groups can return multiple columns. Before filling down, place the formula where the spilled result has room and confirm that blank or malformed rows are handled with IFERROR.
Remember the engine limits
Google Sheets documents that its regex functions use the RE2 engine and do not support Unicode character-class matching. Patterns copied from another regex tool may use features that Sheets does not accept or may behave differently. Test the actual formula in Sheets rather than validating only in a separate website.
Do not convert identifiers by accident
Cleaned phone numbers, ZIP codes, case numbers, and product IDs are often text, not quantities. Format the destination column as Plain text before replacing values when leading zeros or long digit strings must remain exact. The guide How to Stop Google Sheets from Changing Numbers to Dates explains the related data-type issue.
Use a safer cleanup layout
- Keep the original in column A.
- Put the proposed cleaned value in column B.
- Put an OK/REVIEW result in column C.
- Filter REVIEW rows and inspect several edge cases.
- Compare counts, blanks, and duplicates before replacing anything.
- Paste values only after the result is accepted.
Example before-and-after review
| Original | Proposed output | Decision |
|---|---|---|
+1 (555) 010-2040 | +15550102040 | Review country/extension rules |
ABC-00123 | ABC-00123 | Keep as text |
no address supplied | blank | Manual follow-up |
Completion checklist
- The source column is unchanged.
- Anchors and capture groups match the intended scope.
- Blank and malformed rows are visible.
- Leading zeros and identifiers remain text.
- A representative sample and all REVIEW rows were checked.