Data Cleaning Template
Clean messy data in Google Sheets with AI: extract entities, normalize formats, dedupe and classify with =GPT_EXTRACT(), =GPT_FORMAT(), =GPT_CLASSIFY(). Free template.
Fix a messy import without regex gymnastics: extraction, normalization and categorization as drag-down formulas.
Common fixes
| Problem | Formula |
|---|---|
| Emails buried in text | =GPT_EXTRACT(A2, "email address") |
| Inconsistent phone formats | =GPT_FORMAT(A2, "+1 (XXX) XXX-XXXX") |
| Mixed-case names | =GPT_FORMAT(A2, "proper name casing") |
| Free-text categories | =GPT_CLASSIFY(A2, $F$1) — your taxonomy in F1 |
| Ungrouped address parts | =GPT_HSPLIT(A2, "street, city, zip, country") |
| Grammar in descriptions | =GPT_APPLY(A2, "fix grammar and spelling") |
Tips
- Work on a copy of the column; compare before/after with a simple
=A2=B2check column. - For huge sheets, process in chunks and use Replace formulas with results between chunks.
Get started
- Install GPT for Sheets from the Google Workspace Marketplace (free tier included, no API keys needed).
- Copy the template or paste the formulas into your own sheet.
- Drag the formula down your rows, then use Replace all GPT formulas with results in the sidebar.
Function reference: data cleaning functions.
FAQ
How is this different from SmartFill?
SmartFill guesses from patterns; these functions follow explicit instructions and your taxonomy, which makes results reproducible across the whole column.
Can it dedupe records?
Combine =GPT_FORMAT() normalization with classic UNIQUE()/COUNTIF() — normalize first so near-duplicates become exact duplicates.
Is my data sent anywhere?
Cell content in the formula is sent to the selected AI model to compute the answer, same as any AI add-on. See the privacy policy for details.