Messy Data Is Mostly Boring, and a Little of It Is Hard
If you work with exported lists, form responses or CRM dumps, you have seen it: names in capitals, extra spaces, duplicate rows, company names written five ways, addresses typed into a single cell. This guide is for anyone who has to clean that kind of sheet before it can be used.
Here is the approach I take with AI data cleaning. Clean first with normal formulas, because they are instant, free and predictable. Then bring in AI only for the parts rules cannot handle, such as deciding that "Intl Business Machines" and "IBM Corp." are the same company. Most of a messy sheet falls into the first group. A small part falls into the second, and that is where AI earns its place.
I use SheetAI for the AI part, a Google Sheets add-on. The ideas apply to any tool, but the formulas below are SheetAI formulas.
What You Need
- A Google Sheet with the data you want to clean.
- SheetAI installed from the Google Workspace Marketplace. Open it from
Extensions > SheetAI App > Launch. - Either your own API key (OpenAI, Anthropic, Google Gemini and others work, and you pay the provider directly) pasted in the sidebar settings, or a hosted plan with no key, from $12/month. See pricing.
If you are new to the add-on, the get started page walks through the first formula.
Step 1: Clean What Rules Can Already Clean
Before reaching for data cleaning AI, use what Google Sheets already does well. These are deterministic, so the same input always gives the same output.
Trim stray spaces. Leading, trailing and doubled spaces break lookups and duplicate checks.
=TRIM(A2)
Fix capitalisation. =PROPER(A2) turns "JOHN SMITH" into "John Smith". =UPPER(A2) and =LOWER(A2) do the obvious. Watch out for names like "McDonald" or "O Brien", which PROPER will flatten.
Remove duplicates. Select the range and choose Data > Data cleanup > Remove duplicates. Tick the columns that define a duplicate, for example email only. Do this on a copy, because it deletes rows.
Split text into columns. Select a column and use Data > Split text to columns for data separated by commas, semicolons or spaces. Where a fixed separator exists, this beats AI every time.
Extract an email from text. If you need to extract an email from text, a regular expression does it without AI:
=REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")
This returns the first email address in the cell. It returns an error if there is none, so wrap it in IFERROR to leave the cell empty: =IFERROR(REGEXEXTRACT(...), ""). When the pattern is predictable, a regex is the right tool: faster than AI and it cannot invent an address that is not there.
A good way to clean data in Google Sheets is to do all of this first, in this order: trim, fix case, split, remove duplicates. Whatever is left is the hard part.
Step 2: Set Up Your AI Cleaning Column Safely
Before the examples, four habits that make AI cleaning safe.
- Never overwrite the raw column. Keep column A as it came in and write cleaned output to a new column. If the AI gets a row wrong, you can see the original.
- Use temperature 0. The third argument of
=SHEETAI(prompt, max_tokens, temperature)controls randomness. For cleaning you want the same answer every time, so use=SHEETAI(prompt, 200, 0). - Give the allowed values in the prompt. "Choose one of: A, B, C" is far more reliable than "tidy this up".
- Fill in batches. Google Sheets custom functions time out after about 30 seconds per cell, so fill 50 to 100 rows at a time rather than thousands at once. Results are cached, so repeated prompts are not billed twice.
Step 3: Standardise Company Names
Company names are the classic case. A CRM export might contain "IBM", "Intl Business Machines" and "IBM Corp." for the same account. A rule cannot know those are one company. A model usually does.
=SHEETAI("Return the standard company name for this input, with no legal suffixes like Inc, Ltd or Corp. Reply with the name only: "&A2, 100, 0)
| A | B | |
|---|---|---|
| 1 | Company (raw) | Company (clean) |
| 2 | Intl Business Machines | IBM |
| 3 | IBM Corp. | IBM |
| 4 | I.B.M. | IBM |
| 5 | Microsoft Corporation | Microsoft |
| 6 | MSFT | Microsoft |
| 7 | Alphabet Inc. | Alphabet |
Once the column is clean, a pivot table or UNIQUE on it gives you one row per company. If you have a list of the names you want to end up with, put it in the prompt: "Choose from this list, or reply Not in list". That stops the model creating new variants.
Step 4: Split a Free-Text Address
When an address sits in one cell, split text to columns usually fails because the separators are inconsistent. SHEETAI_EXTRACT pulls named fields out of text:
=SHEETAI_EXTRACT(A2, "street, city, postcode, country")
| A | B | |
|---|---|---|
| 1 | Address (raw) | Extracted |
| 2 | 12 Baker Street London NW1 6XE UK | street: 12 Baker Street, city: London, postcode: NW1 6XE, country: United Kingdom |
| 3 | Flat 4, 88 Princes St, Edinburgh EH2 2ER | street: Flat 4, 88 Princes St, city: Edinburgh, postcode: EH2 2ER, country: United Kingdom |
| 4 | 221 Main Street, Springfield | street: 221 Main Street, city: Springfield, postcode: , country: |
| 5 | 5 Rue de Rivoli 75001 Paris France | street: 5 Rue de Rivoli, city: Paris, postcode: 75001, country: France |
| 6 | Unit 7 Dockside Rd Bristol BS1 4RB | street: Unit 7 Dockside Rd, city: Bristol, postcode: BS1 4RB, country: United Kingdom |
Look at the third row. The address has no postcode and no country, and the correct output leaves them empty. That is the behaviour you want. If you need these as separate columns afterwards, you can split the result on the comma with Data > Split text to columns, or pull a field out with REGEXEXTRACT. See the SHEETAI_EXTRACT docs for the details.
Step 5: Pull Contact Details Out of an Email Signature
Pasted signatures are the other mess regex cannot handle well, because the layout changes with every sender.
=SHEETAI_EXTRACT(A2, "name, email, phone")
| A | B | |
|---|---|---|
| 1 | Signature (pasted) | Extracted |
| 2 | Kind regards, Anita Rao | Head of Ops | anita@northwind.co | +44 20 7946 0123 | name: Anita Rao, email: anita@northwind.co, phone: +44 20 7946 0123 |
| 3 | Thanks! Sean OBrien, sean.obrien@fabrikam.com, mob 07700 900123 | name: Sean OBrien, email: sean.obrien@fabrikam.com, phone: 07700 900123 |
| 4 | Best, Li Wei. Sent from my iPhone | name: Li Wei, email: , phone: |
| 5 | Priya Sharma, Sales Lead, priya@contoso.io, 080 4123 5566 | name: Priya Sharma, email: priya@contoso.io, phone: 080 4123 5566 |
| 6 | Cheers, Tom | name: Tom, email: , phone: |
My rule here: if every row contains an email, use REGEXEXTRACT from step 1 for that field and use AI only for the name and phone. The cheaper and stricter method wins wherever it works.
Step 6: Normalise Job Titles Into a Fixed List
Job titles from forms come back as "VP Sales", "Vice President of Sales", "Sales VP" and "head of sales". To report on seniority you need a short list.
=SHEETAI_CLASSIFY(A2, "C-Level, VP, Director, Manager, Individual Contributor, Other")
| A | B | |
|---|---|---|
| 1 | Job title (raw) | Level |
| 2 | Vice President of Sales | VP |
| 3 | Sales VP | VP |
| 4 | Chief Technology Officer | C-Level |
| 5 | Head of Marketing | Director |
| 6 | Software Engineer II | Individual Contributor |
| 7 | Founder and CEO | C-Level |
| 8 | Account Manager | Manager |
SHEETAI_CLASSIFY returns exactly one value from your list, which keeps the column pivot-friendly. Notice that "Head of Marketing" could reasonably be Director or VP depending on the company. That ambiguity is yours to settle: either accept the label, or define what each level means in the list. More on writing good category lists in the guide to classifying data automatically in Google Sheets.
Step 7: Fix Inconsistent Dates and Units
Text like "3rd of March 24", "03/03/2024" and "March 3, 2024" is a job for AI only when the text is truly free-form. First try =DATEVALUE(A2) or change the cell format, since Sheets understands many date formats on its own. Where that fails, ask for one format:
=SHEETAI("Convert this date to YYYY-MM-DD. If the day and month order is ambiguous, reply AMBIGUOUS. Reply with the date only: "&A2, 50, 0)
The AMBIGUOUS escape hatch matters. "03/04/2024" is 3 April in the UK and 4 March in the US, and the model cannot know which you meant. The same trick works for units, for example "Convert to kilograms, reply with a number only: "&A2, and again an instruction to reply NEEDS REVIEW when the unit is unclear. Do the arithmetic itself in normal formulas where you can.
Step 8: Flag Rows That Look Wrong
Sometimes you do not need a fix, only a list of rows a human should look at.
=SHEETAI_CLASSIFY(A2&" | "&B2&" | "&C2, "Looks valid, Needs review")
Join the cells that belong together, so the model sees the whole record. Then filter the column to "Needs review" and read those rows. This is a triage tool, not a verdict: it will miss some problems and flag some good rows. Combine it with plain checks, such as =ISEMAIL(B2) or a length test, which are exact.
Finish: Spot-Check, Then Paste Values
When the cleaned columns are filled:
- Read a sample of rows, including the odd ones. I look at the first ten and at anything the formula flagged.
- Sort the cleaned column and scan the ends, where oddities tend to show up.
- Freeze the results: copy the column and use
Edit > Paste special > Values only. This stops the formulas recalculating later and removes the dependency on the add-on. The guide to preventing recalculation explains why.
Prompt Tips for AI Data Cleaning
- State the output format exactly: "Reply with the name only".
- Always list the allowed values, plus an escape value such as "Not in list" or "NEEDS REVIEW".
- Tell the model what to do when information is missing: "leave blank, do not guess".
- Include only the cells needed. The model sees only what you reference.
- One task per column. A formula that fixes the name, the date and the phone in one go is harder to check.
Limits and When Not to Use AI for Cleaning
It can guess wrong on ambiguous rows. Ambiguous dates, common names and abbreviations such as "MSFT" are risky. Escape values reduce this but do not remove it.
Never use it to invent missing data. AI should reformat what is in the cell, not fill in a postcode or phone number that was not there. If a prompt could make the model "helpfully" supply a value, rewrite it.
Personal data and privacy. Cell text is sent to an AI provider. If your sheet contains personal data, check your privacy policy, your agreements with the provider and any rules from your employer or client before you run it. Remove columns the formula does not need.
Rules are better when the pattern is regular. Emails, phone formats and fixed separators are regex and split territory. AI costs money and is less predictable.
Large files. For very large datasets, a script or a dedicated data tool is a better fit than a sheet.
Frequently Asked Questions
What is AI data cleaning?
It means using a language model to tidy values that rules cannot handle, such as matching company name variants, splitting free-text addresses or mapping job titles to a list. It works best after normal cleaning has dealt with spaces, case and duplicates.
Is AI for data cleaning accurate?
It is good on clear cases and unreliable on ambiguous ones, so spot-check a sample and give the model allowed values and an escape option. I do not quote accuracy figures, because they depend on your data and your prompt.
How do I extract an email from text in Google Sheets?
Use =REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"). If the text is irregular, =SHEETAI_EXTRACT(A2, "name, email, phone") can pull the email along with other fields.
How do I clean data in Google Sheets without AI?
Use =TRIM, =PROPER, Data > Data cleanup > Remove duplicates, Data > Split text to columns and REGEXEXTRACT. These cover a large share of cleaning jobs.
Does cleaning overwrite my original data?
Not if you write to a new column, which is what I recommend. Keep the raw column untouched and paste values into the clean column once you have checked it.
Start With One Column
Pick the messiest column, write one formula in a new column next to it, and run it on ten rows. If the output matches what you would have typed, fill down in batches. You can install SheetAI from the Google Workspace Marketplace or read the get started guide.