ChatGPT Excel Cleanup: Mixed Dates and Decimal Separators
Clean mixed date and decimal formats with ChatGPT. Preserve IDs, flag ambiguous rows, and verify totals before importing the result into Excel.
Why changing the display format can change the wrong problem
An exported table can contain US dates, UK dates and European decimal commas at the same time. The string 03/04/2026 could mean March 4 or April 3; 1,234 could mean one thousand two hundred thirty-four or a decimal value. A global replacement cannot establish which meaning the source intended.
Use ChatGPT to prepare a traceable cleanup and review list. Keep the original, confirm each source's date and number rules, and convert only values supported by those rules. This guide covers one-off cleanup of mixed-source data, building on our broader Excel guide.
1. Confirm the source rules and preserve identifiers
Work on a copy of a file you are allowed to share. Remove unnecessary personal or confidential data. ChatGPT can analyze spreadsheet and CSV files when the relevant features are available to your account; if you cannot attach or generate a file, use a small pasted table.
Ask the file creator to confirm the actual date pattern, decimal separator, thousands separator and currency. Record a source_locale for each source or row. An office location or the language of the document is not proof of the export settings.
- Keep ID as text: 0007 must remain 0007, not 7.
- Preserve date_raw and amount_raw alongside any new columns.
- State the target: YYYY-MM-DD dates and decimal-point amounts without thousands separators.
- Keep currencies separate. This cleanup does not perform currency conversion.
2. Start with this five-row example
These are original fictional records for this guide. The source rules for en-US, en-GB and de-DE have been explicitly confirmed; unknown means the creator has not supplied a rule. All amounts are in EUR. Treat the cells as text before parsing them.
| ID | source_locale | date_raw | amount_raw |
|---|---|---|---|
| 0007 | en-US | 03/04/2026 | 1,234.56 |
| 0008 | en-GB | 03/04/2026 | 1,234.56 |
| 0009 | de-DE | 04.03.2026 | 1.234,56 |
| 0010 | unknown | 03/04/2026 | 1,234 |
| 0011 | en-GB | 31/02/2026 | 12.00 |
3. Run the shared prompt with a clear output contract
Open the complete prompt in the shared board and set the ID, date and amount column names. Add the source rules above, EUR as the currency, and the target formats. Ask for an audit and conversion plan before applying the cleanup.
Request separate columns date_normalized, amount_normalized, row_status and review_reason. An unknown or invalid field stays blank; a row remains pending review if a required field cannot be approved. A confirmed amount can still be retained on a row with an invalid date.
The model should return a full result, a pending-review list and a validation summary. If file creation is available, request these as separate sheets in an XLSX. A generated file still needs the checks below.
Open, edit and copy the complete cleanup prompt
4. Compare the output with the expected values
The table below is an editorial reference, with the dates and arithmetic checked independently for this example. It is not a recorded ChatGPT response or an Excel execution test. The dash means the normalized value should be empty.
| ID | date_normalized | amount_normalized | row_status | review_reason |
|---|---|---|---|---|
| 0007 | 2026-03-04 | 1234.56 | approved | Confirmed en-US rules |
| 0008 | 2026-04-03 | 1234.56 | approved | Confirmed en-GB rules |
| 0009 | 2026-03-04 | 1234.56 | approved | Confirmed de-DE rules |
| 0010 | — | — | pending_review | Source rules missing; both fields ambiguous |
| 0011 | — | 12.00 | pending_review | 31 February is not a valid date |
- Do not change 31 February to 28 February or another plausible date.
- Do not choose the most common interpretation for row 0010. Ask the source creator.
- Only approved rows enter the approved-amount total. Row 0011's 12.00 is retained but excluded.
5. Check the result before using the numbers
For this example, the approved total is 1234.56 × 3 = 3703.68 EUR. An output that includes the pending 12.00 will incorrectly report 3715.68 EUR as the approved total.
- Input and output both contain five rows, in the same ID order.
- All five IDs and the original date and amount strings are unchanged.
- There are three approved rows and two pending-review rows.
- The approved EUR total is 3703.68; pending rows are reported separately.
- Every conversion names its confirmed source rule, and every empty normalized field has a reason.
6. Import the result into Excel without losing the checks
When a CSV is opened directly, Excel can interpret its columns using current default settings. Use Data > From Text/CSV and inspect the delimiter and column values. Choose Transform Data when you need to control the types before loading.
Set ID and the raw columns to Text before transformations reinterpret them. Check any automatically added Changed Type step; if it has already removed zeros or misread a date, go back to the untouched source rather than reconstructing the value.
In Power Query, Change Type > Using Locale lets you choose the data type and the source locale for a conversion. Apply it to groups with the same confirmed source rules. One locale setting does not establish the origin of every value in a mixed-source column.
After loading, verify that ID is text, approved amounts are numeric, and dates represent the intended day and month. Recheck the five-row counts and approved total. Seeing a decimal comma in your local Excel display does not by itself mean the underlying number is wrong.
If you request a CSV, specify its delimiter, encoding and quoting rules; values containing the delimiter need quoting. Prefer a multi-sheet XLSX when you need to keep the original, cleaned result, pending rows and validation together. Excel CSV export saves only the current sheet.
Related guide and official references
Official references support the product features and import behavior. The five-row example and review procedure were created by Praclane.
