The task
You have an export (customer names, addresses, free-text tags, product titles) that arrived in spectacular condition: mixed case, leading whitespace, typos, “John Smith” and “john smith” sitting next to each other. You want a clean column to filter, join, or report on. Manual cleanup with regex or Find & Replace is slow; AI can do it in one prompt, if you sample first and verify before scaling.
When AI helps — and when it does not
AI is excellent at fuzzy normalisation, deduplication, and obvious-typo correction. It is poor and dangerous at changing meaning without flagging: it may “fix” “Sun” to “Sunday,” “co” to “Colorado,” or “Apple” to “Apple Inc.”, sometimes correctly, sometimes catastrophically. Always run on a sample, verify, and tell AI which transformations are allowed.
What to feed the AI
- A small sample (10-20 rows) of the messy column
- The desired output format (lowercase, Title Case, exact “Firstname Lastname”)
- What is not allowed to change (“do not expand abbreviations,” “do not infer country”)
- A small “good output” example so AI knows what success looks like
- Any reference list it should match against (a controlled vocabulary, a list of valid cities)
- Whether to flag rows it’s unsure about, or to make a best guess
Copy-ready prompt
Clean this messy text column.
Desired output: <format and examples>
Allowed transformations: <normalise case / trim whitespace / fix obvious typos / dedupe>
Not allowed: <expand abbreviations / infer missing fields / merge names / change meaning>
Reference list (must match if applicable): <list>
Unsure rows: <flag with [REVIEW: reason] or best-guess>
Sample (10-20 rows):
"""
<paste>
"""
Good output example:
"""
<paste 3-5 cleaned rows>
"""
Return:
1. Cleaned sample, row-aligned with the input
2. A "diff log" — for every row you changed, the original, the new value, and the reason
3. A "review queue" — rows where you were unsure
4. Whether the same prompt is safe to apply to the full column
Do not change a value to fit a pattern unless you are confident. Flag instead.For 1000+ row jobs: “Once I confirm the sample, give me the exact transformation rules so I can run them via formula, not by re-prompting.”
Recommended output structure
Cleaned sample, diff log (original / new / reason), review queue (rows that need human eyes), and a verdict on full-column safety. The diff log is the single most valuable output. Without it, mistakes propagate invisibly.
How to check the output is usable
- Run the same prompt twice on the same sample. Outputs should match
- Spot-check 5 random rows in the diff log
- Review queue exists. AI is too confident if it has zero unsure rows
- Reference list matching is exact (no near-matches without flagging)
- No row’s meaning changed (Sun → Sunday is a meaning change; sun → Sun is just case)
Common mistakes
- Pasting the whole column without sampling. You discover errors after the fact
- No “good output” example. AI guesses at what you want
- Letting AI expand abbreviations. “USA” to “United States of America” can break downstream joins
- No diff log. Corrections happen invisibly
- Skipping the review queue. Flagged rows are not failures; they are the careful path
Practical depth notes
For How to Use AI to Clean Up Messy Excel Columns: Names, Cases, Typos, Duplicates, the difference between a usable AI result and a generic one is the input packet. Give the model the audience, the current draft or raw material, the desired format, the decision you need to make, and two examples of what good and bad output look like. Ask it to preserve facts first, then improve structure or wording second.
After the first response, do a separate review pass. Look for missing constraints, invented details, weak calls to action, and language that sounds plausible but does not match the real situation. The best final output should be easy to use immediately: clear owner, clear next step, and no hidden assumption that someone else has to untangle. A stronger version of this workflow also defines the handoff. Decide who will use the output, what they should do next, and what information would make them reject it. If the deliverable is copy, test whether it has a single clear action. If it is analysis, test whether it separates observation from recommendation. If it is planning, test whether dates, owners, and tradeoffs are explicit enough for someone else to execute.
FAQ
- Should I use AI or formulas? Formulas for deterministic rules; AI for fuzzy. Use both: AI to find the patterns, formulas to scale.
- What about PII? Be cautious. Hash before pasting if the data is sensitive.
- Will AI dedupe based on similarity? Yes, but verify the similarity rule. “John Smith” / “J. Smith” may or may not be the same person.
Related
- Table explanation — make a clean table digestible
- Chart takeaway — once cleaned, summarise the chart
- Spreadsheet error diagnosis prompts — when formulas misbehave
- Excel formula explanation prompts — understand existing formulas
- Excel analysis prompts — deeper analysis after cleanup
- ChatGPT spreadsheet help — tool walkthrough
- AI table explainer tutorial — full table workflow