Coding · Spreadsheets

Clean Up Messy Data in a Spreadsheet

Tidy names, dates, phone numbers and duplicates in your sheet using easy formulas and built-in tools.

Prompt Code output

I have a messy list in [SPREADSHEET_APP]. Here are a few example rows, with personal details replaced by fake ones: [SAMPLE_ROWS]. The problems I see are: [PROBLEMS]. Please show me how to clean this up. For each problem, give me either a formula to put in a new helper column or the menu steps for a built-in tool (such as removing duplicates or splitting text into columns). Explain where to click and what each step does in plain words. Remind me to make a copy of the sheet first so I can go back if something goes wrong. Finally, tell me how to turn the cleaned formulas into plain values so I can delete the old columns safely.

Fill in the brackets

[SPREADSHEET_APP]
Excel or Google Sheets
[SAMPLE_ROWS]
Paste 3 to 5 rows with fake names, e.g. ' john SMITH ', 03/04/24, +44 7700…
[PROBLEMS]
e.g. extra spaces, mixed upper and lower case, dates in different formats, repeated rows

How to use

  1. Copy the prompt.
  2. Replace the text inside [BRACKETS] with your own details.
  3. Paste it into ChatGPT or another AI assistant.
  4. Read the result and ask for changes if needed.

Tip: Replace real names, emails and phone numbers with made-up ones before pasting your sample rows.