Prompts to Clean Messy Data: 18 Copy-Paste Prompts That Ask for the Fix, Not the File

The best prompts to clean messy data share four rules: ask for a formula or script rather than cleaned rows; say "add a new column, do not overwrite"; require a row count before and after; and require ambiguous values (04/05/2026, a name with two commas) to be flagged rather than guessed. A prompt built that way gives you something you can read, test and keep. A prompt that says "clean this" gives you a new copy of your data with invisible decisions in it.

Every prompt below follows those rules. Seven of them we actually ran on October 10, 2026, in a fresh session of Claude (Sonnet 5.5 via the Claude Agent SDK, no tools), and then executed the returned scripts with Python 3 and pandas, or checked the returned formulas by hand against the sample values. Tested prompts are marked, with what came back. The rest follow the same pattern and have not been run; treat them as templates. For the privacy side (what you may paste, and how to redact a sample), see how to clean a CSV with AI without uploading it.

Dates

1. Mixed date formats to ISO, with ambiguity flags (tested)

I have a CSV called orders.csv with these columns: Order ID, Customer Name, Order Date, Amount, Product Category, Status, Notes.

The Order Date column mixes formats. Sample values: 2024-01-15, 01/20/2024, 02/10/2024, 2024-03-01, 03/10/2024.

Write a Python script using pandas that:
1. Reads orders.csv with every column as text (dtype=str).
2. Adds a NEW column called Order Date ISO in YYYY-MM-DD format. Do not overwrite Order Date.
3. Treats slash dates as US month/day/year. If a slash date could also be read as day/month (both parts 12 or under), still convert it as US but put the word AMBIGUOUS in a new column called Date Flag for that row. Otherwise leave Date Flag empty.
4. Leaves any value it cannot parse unchanged and puts UNPARSED in Date Flag.
5. Prints the row count before and after, and the count of rows flagged.
6. Writes the result to orders_dated.csv.
Do not drop any rows.

Result: the script ran first time on our 13-row sample. 13 rows in, 13 out, 3 flagged: two AMBIGUOUS (02/10/2024 and 03/10/2024) and one UNPARSED (a fully empty row). Unflagged slash dates like 01/20/2024 converted cleanly. One nice touch we did not ask for: it used keep_default_na=False so that N/A and NULL stayed as text instead of becoming empty cells. One quirk it pointed out itself: a date like 03/03/2024 is flagged AMBIGUOUS under the rule as written even though both readings agree.

2. Excel formula version

Excel 365. Column C has dates as text in either YYYY-MM-DD or M/D/YYYY. Give me a formula for a new column D that returns a real Excel date for both formats, treating slash dates as US month/day/year, and returns the text CHECK if the cell is blank, cannot be parsed, or is a slash date where both the month and day parts are 12 or under. Do not overwrite column C. Then give me a COUNTIF for the number of CHECK rows.

3. Excel serial numbers back to dates

A CSV exported from Excel has a Date column where some cells are serial numbers like 45292 and others are text like 2024-01-01. Write a pandas snippet that adds a Date ISO column: serial numbers converted using Excel's 1900 epoch, ISO strings kept, anything else left blank and flagged SERIAL_UNPARSED in a Flags column. Print how many of each kind it found. Do not overwrite the original column.

Currency and numbers

4. Dollar signs, commas and null words to a numeric column (tested)

In Excel, column D (Amount) has text values like $89.99, 125.50, NULL, $45.00, null, 199.99, N/A, 320.00, and some blanks. Row 1 is the header, data starts in row 2.

Give me a single formula for a new helper column E that returns the numeric amount when D is a number with or without a leading dollar sign and commas, and returns the text CHECK for anything else (NULL, null, N/A, blank, or other text). Do not overwrite column D. The formula must work in Excel 365. Explain in one or two sentences how to confirm it worked, including a row count check.

=IF(ISNUMBER(D2), D2, IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(D2),"$",""),",","")), "CHECK"))

Result: that formula came back, with a suggested check of =COUNT(E2:E1000)+COUNTIF(E2:E1000,"CHECK") against the number of data rows. We traced it by hand against every sample value: $89.99 to 89.99, 125.50 unchanged, NULL/null/N/A/blank to CHECK. It also warned that filling the formula past the last data row inflates the CHECK count, which is true. We did not open Excel for this test; the logic was checked on paper.

5. Parentheses negatives and European decimals

Write a pandas function clean_amount(text) that returns a Decimal or None. Rules: strip currency symbols and spaces; (123.45) means -123.45; a trailing minus like 123.45- means negative; if the string contains a comma followed by exactly two digits at the end and no period (e.g. 1.234,56) treat it as European and convert; otherwise commas are thousands separators. Return None for anything else. Then show how to apply it to column Amount into a new column Amount Clean, flag None results as UNPARSED in a Flags column, and print the count of unparsed rows and the sum of the parsed ones.

6. Leading zeros that Excel will eat

My CSV has a ZIP column and a SKU column where some values start with zeros (02134, 00451). Give me a pandas snippet that reads the file with every column as text, reports how many values in each of those columns start with a zero, and writes the file back so that Excel will keep the zeros when the CSV is opened (explain the trick you use and its downside). Do not change any other column.

Duplicates

7. Flag duplicates by a normalised key, do not delete (tested)

Write a Python pandas script that reads contacts.csv (all columns as text) and finds duplicate rows by the Email column, ignoring case and surrounding whitespace. Do NOT delete anything. Add a new column Dup Group that holds the same number for every row in a duplicate group and is empty for unique rows. Print the total row count, the number of duplicate groups, and the number of rows that would be removed if only the first row of each group were kept. Write the result to contacts_flagged.csv with the same row count as the input.

Result: on an 8-row test file with two groups of duplicates (one differing only by case and padding) and two rows with blank emails, the script reported 8 rows, 2 groups, 3 rows removable, and wrote 8 rows out with Dup Group filled correctly. It deliberately did not group the blank emails together, and said so. We would have wanted that choice surfaced as a question, but it was at least stated.

8. Fuzzy duplicates, review list only

Write a pandas script that lists PAIRS of rows in vendors.csv whose Vendor Name values are likely the same company (case-insensitive, ignoring punctuation, whitespace and the words Inc, LLC, Ltd, Co). Output a review file pairs.csv with both row numbers, both original names and the normalised key. Do not merge or delete anything. Print the number of pairs found and the row count of the input, which must be unchanged.

Names, case and whitespace

9. Trim, non-breaking spaces and proper case (tested)

Excel 365. Column B (Customer Name) has values with leading/trailing spaces, double spaces inside, and some non-breaking spaces (character 160) pasted from a web page. Give me one formula for a new column C that removes leading and trailing spaces, converts non-breaking spaces to normal spaces, collapses repeated internal spaces to one, and then applies proper case. Do not overwrite column B. Also tell me a formula for column D that returns TRUE when C differs from B, so I can count how many rows changed.

=PROPER(TRIM(SUBSTITUTE(B2, CHAR(160), " ")))
=NOT(EXACT(B2, C2))

Result: correct, and it explained why EXACT rather than B2<>C2 (the <> operator is case-insensitive, so a row where only capitalisation changed would be missed). It also warned, unprompted, that PROPER turns "McDonald" into "Mcdonald" and can mangle apostrophes. That warning is the useful part: proper-casing names is lossy, and a prompt that asks for it should also ask for a review flag on names containing an internal capital or an apostrophe.

10. Standardise category values to a fixed list

Column E (Product Category) has values like Electronics, electronics, ELECTRONICS, Clothing with trailing spaces, Home & Garden, home & garden, and blanks. The allowed values are exactly: Electronics, Clothing, Home & Garden. Write a pandas snippet that adds a Category Clean column mapped to the allowed list using a case-insensitive, whitespace-trimmed comparison, leaves Category Clean empty and sets Flags to UNMAPPED for anything that does not match, and prints a table of every distinct original value with its mapped result and count. Do not overwrite column E.

11. Standardise null words

Give me a pandas snippet that treats the strings NULL, null, N/A, n/a, None, -- and whitespace-only cells as empty in every column, but ONLY in a copy of the dataframe, and prints, per column, how many cells it would blank. Do not write any file. I want to review the counts before deciding.

Headers

12. Snake-case headers with collision detection (tested)

Write a short Python pandas script that reads export.csv with all columns as text, renames every column header to snake_case (lowercase, spaces and punctuation replaced by single underscores, no leading or trailing underscores), prints a two-column table of old header to new header, refuses to continue and prints an error if two old headers would collapse to the same new name, and otherwise writes export_renamed.csv. Print the row count before and after.

Result: worked first time. "Order ID" became order_id, "Product Category" became product_category, 13 rows in and out, and the old-to-new table printed. It also noted a gap in our prompt: pandas renames exact duplicate headers to a and a.1 on read, so two identical source headers would not be caught by the collision check. Good catch; if your exports can contain duplicate headers, add "read with header=None" to the prompt.

13. Map headers to a target schema

My target columns are: date, description, amount. My export headers are: Posted Date, Memo, Debit, Credit. Write a pandas snippet that builds a new dataframe with the target columns, where amount = Credit minus Debit (both read as text, blanks as zero, flag UNPARSED if either is non-numeric), and prints the mapping it used plus the row count. Do not modify the original dataframe.

Splitting and merging columns

14. Split "Last, First" on the first comma only (tested)

Excel 365. Column A has full names in the form "Last, First" (for example "Smith, John"). Some cells have no comma (for example "Madonna") and some have two commas (for example "Smith, John, Jr."). Give me formulas for column B (Last) and column C (First) that split on the first comma only, trim spaces, and return the text CHECK in both B and C when the cell has no comma or more than one comma. Do not overwrite column A.

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))<>1,"CHECK",TRIM(LEFT(A2,FIND(",",A2)-1)))
=IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))<>1,"CHECK",TRIM(MID(A2,FIND(",",A2)+1,LEN(A2))))

Result: correct on all three sample values when traced by hand (Smith, John splits; Madonna and Smith, John, Jr. both return CHECK). It also pointed out that an empty cell returns CHECK, and offered a wrapper to leave blanks blank.

15. Combine address parts

Columns: Street, City, State, ZIP (all text, ZIP may have leading zeros). Give me an Excel 365 formula for a new column Full Address that joins them as "Street, City, State ZIP", skips empty parts without leaving stray commas, and returns CHECK if City or State is blank. Do not change the original columns.

Encoding and hidden characters

16. Detect and report mojibake and invisible characters

Write a pandas script that reads data.csv as text and, without changing anything, prints per column: the count of cells containing a non-breaking space (U+00A0), a zero-width space (U+200B), a byte-order mark, curly quotes, or the mojibake sequences é and ’. Then print the first 5 example rows for each finding. I will decide what to fix after seeing the report.

17. Re-encode safely

My file was saved as cp1252 but the importer wants UTF-8 without a BOM. Give me a Python snippet that reads it with the right encoding, writes data_utf8.csv as UTF-8 with no BOM, and then re-reads both files and asserts the row counts match and that a sample of 20 random rows is identical cell by cell. Print the result of the assertions.

A prompt that asks for the wrong thing (tested, for contrast)

18. "Clean this data"

Clean this data and give me back the cleaned CSV:

[13 rows pasted]

Result: we pasted our 13-row messy sales sample. The assistant trimmed whitespace, blanked NULL/null/N/A/None/-- in three columns, removed the empty row, and left dates, case and dollar signs alone, with a short note of what it changed. Twelve rows came back. That is a reasonable result, and it is also a copy of the data with the rules in the assistant's head rather than in a script. No row counts, no diff, nothing to re-run next month. Prompts 1 to 17 exist so that you never need prompt 18.

When a prompt is the wrong tool

A prompt is right for a one-off file and a problem you can describe. It is the wrong tool when the same export arrives every week or month, because you will either re-prompt (and get slightly different code) or maintain a growing folder of scripts. At that point the fix is a saved cleanup: a named sequence of steps you run on each new file, with a preview of what changed. Our guide to reusable monthly CSV cleaning pipelines covers that pattern, and the vendor catalog CSV cleaner page shows a typical pipeline for product and supplier lists, where prompts 10, 12 and 13 above become fixed steps you own.