Skip to content
Data & SpreadsheetsIntermediate

Diagnose and Clean Messy Data

Paste a sample of messy data and get a list of problems, a cleaning plan, and step-by-step instructions or formulas to fix it.

Share on X (opens in a new tab)Share on LinkedIn (opens in a new tab)

Make it yours

Fill in each variable and watch the prompt update. Anything still in {{braces}} needs your input before you use it.

Fill in the blanks

0 of 3 filled

Where you'll do the cleaning.

What the data is for; affects what counts as 'clean'.

10–20 representative rows, including the messy ones.

Live previewUnfilled
You are a data-cleaning expert. Below is a sample of a dataset I need to clean.

Tool I'll use to clean it: {{tool}}
What the clean data will be used for: {{purpose}}

Sample rows:
"""
{{data_sample}}
"""

Please:
1. **Diagnose** — list every data-quality problem you see (inconsistent formats, duplicates, missing values, typos, mixed units, extra spaces, etc.), with an example from the sample for each.
2. **Ask or assume** — for any fix that needs a judgment call (e.g. which duplicate to keep), state the decision you'd make and why, so I can override it.
3. **Cleaning steps** — ordered steps to fix each problem in {{tool}}, including exact formulas or code.
4. **Cleaned sample** — show the sample rows after cleaning, as a table.
5. **Checks** — 2–3 checks I should run on the full dataset, since this is only a sample.

Don't drop or change rows silently — mention every row you would remove or alter.
Open in Playground

Example

Example input

Filled prompt
You are a data-cleaning expert. Below is a sample of a dataset I need to clean.

Tool I'll use to clean it: Excel
What the clean data will be used for: a mail merge for event invitations

Sample rows:
"""
Name,Email,Phone,Signup Date
john smith,JOHN@MAIL.COM,555-0101,03/04/2026
Maria Lopez , maria@mail.com,(555) 0102,2026-03-05
John Smith,john@mail.com,5550101,4 March 2026
Lee Chen,,555 0103,2026-03-07
"""

Please:
1. **Diagnose** — list every data-quality problem you see (inconsistent formats, duplicates, missing values, typos, mixed units, extra spaces, etc.), with an example from the sample for each.
2. **Ask or assume** — for any fix that needs a judgment call (e.g. which duplicate to keep), state the decision you'd make and why, so I can override it.
3. **Cleaning steps** — ordered steps to fix each problem in Excel, including exact formulas or code.
4. **Cleaned sample** — show the sample rows after cleaning, as a table.
5. **Checks** — 2–3 checks I should run on the full dataset, since this is only a sample.

Don't drop or change rows silently — mention every row you would remove or alter.

Example output

Illustrative example response — written by Promptly, not generated live

1. Diagnose

  • Inconsistent name case — "john smith"
  • Extra spaces — "Maria Lopez "
  • Email case varies — "JOHN@MAIL.COM"
  • Phone formats differ — "(555) 0102", "5550101", "555 0103"
  • Mixed date formats — "03/04/2026", "2026-03-05", "4 March 2026"
  • Likely duplicate — rows 1 and 3 (same person, same email after lowercasing)
  • Missing email — Lee Chen

2. Judgment calls

  • Duplicates: keep the first occurrence by email. Override if you prefer the most recent.
  • Ambiguous date "03/04/2026": row 3 suggests 4 March, so I'll assume month/day — please confirm.
  • Missing email: flag, not delete, since it's useless for mail merge but may matter elsewhere.

3. Steps (Excel)

  1. Name: =PROPER(TRIM(A2))
  2. Email: =LOWER(TRIM(B2))
  3. Phone: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")","")," ",""),"-","")
  4. Dates: convert text dates with Data > Text to Columns or DATEVALUE, then format as YYYY-MM-DD
  5. Remove duplicates on the cleaned Email column (Data > Remove Duplicates)

4. Cleaned sample

NameEmailPhoneSignup Date
John Smithjohn@mail.com55501012026-03-04
Maria Lopezmaria@mail.com55501022026-03-05
Lee Chen(missing — flagged)55501032026-03-07

Removed: row 3 (duplicate of John Smith).

5. Checks Count blank emails; count rows before/after dedupe; scan for dates outside the expected range.

Why it works

A real data sample lets the model spot actual problems instead of listing generic advice. Separating diagnosis, judgment calls, and steps keeps you in control of decisions that need human context (like which duplicate to keep). The "no silent changes" rule and the full-dataset checks matter because a sample may not reveal every problem.

Techniques used