Clean Up Messy Spreadsheet Data with ChatGPT
Upload the CSV to ChatGPT data analysis, describe the mess once, and get back a cleaned file plus a repeatable formula or script. Limits and traps.
On this page4 sections
Inconsistent names, mixed date formats, first and last name jammed into one column — this is one of the few tasks where an AI beats writing formulas by hand. Upload the file rather than pasting rows: ChatGPT’s data analysis reads the spreadsheet, writes and runs code against it, and hands back a real file.
1. Upload the file
CSVs and spreadsheets are capped at roughly 50 MB, depending on row size. Free accounts get 3 file uploads per day; paid accounts get up to 80 every 3 hours. OpenAI’s guidance is to upload structured data with clear column names and one record per row — a sheet with merged cells and a decorative header is where these sessions go wrong.
2. Describe the mess as rules
This is customer data. Clean it and return a downloadable CSV:
- Title-case the names, but keep McDonald and O'Brien correct
- Split "Full Name" into First and Last; put anything extra in a Middle column
- Convert every date to YYYY-MM-DD; flag ambiguous ones instead of guessing
- Leave rows you can't parse untouched and list them at the end
What it does: gives explicit rules plus an escape hatch, so ambiguous rows get flagged rather than silently invented. That last line is what stops most bad output.
3. Ask for a repeatable recipe
The cleaned file is a one-off. Ask for the transformation too, so next month’s export takes seconds:
Now give me the pandas code you used, and a Google Sheets formula that splits
column A (full name) into first and last in columns B and C.
What it does: turns a manual cleanup into something you can re-run. OpenAI also ships a spreadsheet-native ChatGPT for Excel and Google Sheets if you’d rather stay in the sheet.
Verify it worked
Check the row count first — it should match, and a mismatch means rows were dropped. Then read
the flagged list and spot-check ambiguous dates (03/04/2026) and non-Anglo names, which is
where a confident wrong answer hides. Row counts and totals are cheap to verify and worth
treating as a tiny eval you re-run each time.
Don’t upload customer data to a tool your policy hasn’t cleared. For that, run the same prompt against a local model. Next: analyze a CSV in ChatGPT or write Excel formulas with ChatGPT.
Source: Data analysis with ChatGPT.