The prompt
Score 10 of 10, Excellent
Use it when An export or a shared sheet has inconsistent dates, names, categories or numbers, and you want rules you can apply and check rather than a silently altered file.
- {{columns}}
- {{sample_rows}}
- {{target_format}}
- {{data_source}}
- {{downstream_use}}
- {{tool}}
Full prompt and why it works
System prompt
You are a data analyst who cleans data carefully and reversibly. You never change a value without a rule that explains it, and you flag what cannot be cleaned with confidence.
User message
<columns_to_clean> {{columns}} </columns_to_clean> <sample_rows> {{sample_rows}} </sample_rows> <target_format> {{target_format}} </target_format> The sample above comes from {{data_source}}. I need these columns cleaned so that {{downstream_use}}. I will apply the cleanup myself in {{tool}}, so I need rules and code, not a cleaned copy of the sample. For each column, write: 1. Problems found: each distinct problem in the sample, with two or three example values copied exactly, such as mixed date formats, trailing spaces, inconsistent capitalization, spelling variants of one category, or numbers stored as text. 2. Cleaning rules: a numbered list, in the order they should be applied, each specific enough to apply by hand. 3. Code: one code block for {{tool}} that applies the rules to the whole column and writes the result to a new column, leaving the original untouched so every change can be compared. 4. Values to review by hand: anything a rule cannot decide safely. Then give one validation check per column: a count or test I can run after cleaning to confirm it worked, for example "no value in order_date is in the future". A good result is one I can apply to the full file and verify with those checks, without having to trust your reading of each row. Do not merge or delete rows, and do not fill blanks with guesses: a blank stays blank unless a rule gives a definite value. Treat a date such as 03/04/2025 as ambiguous unless other values in the same column prove the order of day and month; if it is ambiguous, say so and list it under values to review. Keep the whole answer under 700 words plus code.
Variables
- {{columns}}
- The column names to clean, each with what it should contain, for example "signup_date: the date the customer signed up".
- {{sample_rows}}
- 30 to 100 rows copied from the sheet, including the messiest ones you have noticed. Remove personal data first.
- {{target_format}}
- The format each column should end up in, for example "dates as YYYY-MM-DD, country names in English, amounts as plain numbers".
- {{data_source}}
- Where the data came from, for example "a CRM export merged from two regions".
- {{downstream_use}}
- What must work once it is clean, for example "the monthly revenue pivot groups every region correctly".
- {{tool}}
- Where you will clean it, for example Excel formulas, Power Query, Google Sheets, pandas or SQL.
Why it works
- What to avoid. Rows are never merged or deleted and blanks are never filled with guesses, and each rule writes to a new column so the original stays available for comparison.
- If unsure. Dates like 03/04/2025 are treated as ambiguous unless the column proves the order, the most common way a date cleanup quietly goes wrong.
- What good looks like. Each column ends with a validation check you can run, so you confirm the cleanup on the whole file instead of trusting the sample.