Clean a Messy Customer Spreadsheet With AI for €0, Safely
Updated: November 2026 · Prices checked: November 2026
In short
- Do the boring part with free built-in tools first: Trim whitespace, Remove duplicates and Cleanup suggestions in Google Sheets, or Remove Duplicates in Excel.
- Use a free AI chat for the rules, not the people: paste 20 fake rows that show the mess and get formulas back. Never paste the real customer list.
- For categories, send the AI only the list of unique category names. It returns a mapping table you apply with XLOOKUP.
- Work on a copy, add a row ID column first and compare row counts at the end.
Every small business has that file. Customers typed in five ways, the same person three times, phones with and without +359, and forty spellings of “accessories”. You can clean it for €0 with free spreadsheet features and a free AI chat, without sending your customers’ personal data to anyone. The order of the steps is what keeps it safe.
What you save
The paid shortcut is AI inside the spreadsheet. Gemini in Google Sheets needs an eligible Workspace or Google AI plan; Google AI Pro is listed at €21.99 a month. The other paid shortcut is a person: a freelancer or an assistant who cleans the file by hand for hours.
The maths: if you clean your customer list twice a year, Google AI Pro just for that costs €21.99 × 12 = €263.88 a year, or about €132 per cleanup. The workflow below costs €0 and keeps the personal data on your own computer.
| Mess | Free tool that fixes it | Where AI helps |
|---|---|---|
| Extra spaces | Sheets: Data → Data clean-up → Trim whitespace; TRIM() | Not needed |
| Exact duplicates | Sheets: Data → Data clean-up → Remove duplicates; Excel: Data → Remove Duplicates | Not needed |
| Near-duplicates (case, spaces, dots) | Helper “key” column + COUNTIF | Writes the key formula |
| Name capitals | PROPER(TRIM()) | Handles exceptions you describe |
| Phone formats | Formula | Writes the formula from fake examples |
| 40 spellings of a category | UNIQUE() + XLOOKUP | Builds the mapping table |
Step by step: the safe workflow
- Make a copy. In Google Sheets: File → Make a copy. In Excel: File → Save As with a new name. Never clean the only version. The professor who lost a year of notes to an AI edit is the reason this is step one.
- Add a row ID. Insert column A, type 1 in A2 and fill down. Write down the total row count. You will compare it at the end.
- Run the built-in cleanup. In Sheets select the data and use Data → Data clean-up → Trim whitespace, then Cleanup suggestions. Google says Trim whitespace does not remove non-breaking spaces, so pasted web data may still need a formula.
- Build key columns for duplicates. Exact duplicate removal misses “[email protected] ” vs “[email protected]”. Add a key column such as
=LOWER(TRIM(D2))for email and a digits-only phone key. Then flag repeats with=COUNTIF(K:K, K2)>1. - Review flagged rows, then delete. Sort by the key, look at each group, keep the newest or most complete row. Only now use Remove duplicates on the key column. In Excel, Remove Duplicates deletes permanently, and Microsoft notes it compares the value displayed in the cell.
- Normalize names, phones and categories with the AI prompts below.
- Check. Count rows again. Total before minus duplicates removed must equal total after. Spot-check 10 random IDs against the original file.
Prompts for names, phones and categories
The rule: show the AI the pattern, not your customers. Make up 15–20 rows that copy the real mess. Same formats, fake names and numbers.
I use Google Sheets (comma separators). Column C has phone numbers in many formats.
Fake examples of what I have:
0888 123 456
+359888123456
00359 88 812 3456
888-123-456
(02) 987 6543
Write one formula for D2 that turns any of these into the format +359 88 812 3456 for mobiles
and +359 2 987 6543 for Sofia landlines. If a value does not fit any pattern, return "CHECK".
Explain the logic and list the cases where it will fail.The “CHECK” fallback matters. You want the formula to admit what it cannot fix. Filter column D for CHECK and fix those by hand.
For categories, do not send rows at all. In an empty column type =UNIQUE(F2:F). You now have only the category names, with no personal data. Copy that list into the chat:
Here are all category values from my product sheet, one per line.
Map each to exactly one of these clean categories: Furniture, Lighting, Textiles, Accessories, Other.
Return a two-column table, tab-separated: Original | Clean.
If you are not sure, write REVIEW in the Clean column.
[paste list]Paste the table into a new sheet called Map. Then in the main sheet: =XLOOKUP(F2, Map!A:A, Map!B:B, "REVIEW"). Every row gets a clean category, and anything new or odd shows REVIEW. For shops, clean categories feed straight into the ideas in our guide on AI for an online shop.
For names, ask for a formula that applies PROPER(TRIM()) and then fixes the exceptions you list, such as company suffixes (“EOOD”, “Ltd”) that must stay in capitals.
Limits and traps
- Personal data. A customer list with names, emails and phones is personal data under GDPR. Pasting it into a free consumer chat is the one thing this hack is designed to avoid. Read what never to share with AI chatbots before you make an exception.
- If you must process real rows, do it on your own machine. A local model like the one in our Qwen 27B in LM Studio hack never sends the data out.
- AI merges people who are different. Two “Maria Ivanova” rows can be two customers. Deduplicate on email or phone keys, never on names alone.
- Formulas break on new patterns. The phone formula works for the formats you showed it. Keep the CHECK fallback.
- Delete last. Flag, review, then delete. Removing duplicates in Excel is permanent in that file.
When to pay instead
Pay for built-in AI when you clean data every week, not twice a year. Gemini in Sheets or Copilot in Excel see the real columns and can apply fixes in place, which saves the copy-paste loop. Business plans also come with different data terms than free consumer chats; Anthropic, for example, says it does not train on commercial product data by default. Claude can also now build and edit spreadsheets directly.
And if the list feeds a CRM, clean it once here, then fix the input form so the mess stops at the source: a dropdown for categories and a validated phone field.
FAQ
Can I upload my customer list to ChatGPT to clean it?
Better not on a free consumer account. Names, emails and phones are personal data. Send the AI fake example rows instead and ask for formulas, or send only the unique category values. If real rows must be processed, use a business plan with suitable data terms or a local model.
How do I remove duplicates in Google Sheets?
Select the range, then Data → Data clean-up → Remove duplicates, choose the columns to compare and tick that the data has a header. It catches exact matches only. For near-duplicates, first build a key column with LOWER(TRIM()) and remove duplicates on that.
Why does UNIQUE still show duplicates?
Usually hidden characters. Google notes that trailing spaces and inconsistent number formatting make rows look identical when they are not. Run Trim whitespace first, and if the data came from a website, strip non-breaking spaces with SUBSTITUTE(A2, CHAR(160), ” “).
How long does a cleanup take?
It depends on how messy the file is. The built-in steps take minutes. Most of the time goes into reviewing flagged duplicates and the CHECK and REVIEW rows. That review is the part you should not skip or hand to AI.
Read also: Find Forgotten Subscriptions for €0 With AI and a Bank Export · 500 Product Titles and Meta Descriptions With AI for Under €1 · Track Competitor Prices for €0 With Free Tools and AI
Ivan’s Take
Most people get this backwards. They paste the whole customer file into a chat and ask it to “clean this”. That leaks data and gives you a result you cannot audit. Let the spreadsheet do the mechanical work, let AI write the rules from fake examples, and keep a human on the final review. It is slower by ten minutes and safer by a mile.
Sources: Google: Split text, remove duplicates, trim whitespace · Google: Smart cleanup · Google: UNIQUE · Microsoft: Remove duplicate values · Google AI plans (EUR) · Claude: Data retention
HawkValue
If it doesn’t save you time or money, you won’t see it here
- Facts, not hypeEvery price and number is checked against the official source
- Always the real priceYou see what it costs before you click
- I say when it’s weakEven when it’s OpenAI or Google
