The useful result
Create cleaned date, name and currency columns beside the originals, with checks that prove nothing was lost.
- Create cleaned date, name and currency columns beside the originals, with checks that prove nothing was lost.
One column holds “03/04/2026”, “3 April 2026” and “April 3rd”. Names appear as “SMITH, john”, “john smith” and “J. Smith”. Prices come with different symbols. Pasting the whole sheet into a chat and asking for a clean copy is risky: a model can alter values it should not touch and you will not see where.
A safer approach is to ask for a rule or formula you can read and test, keep the original column beside the new one and decide in advance how ambiguous values are to be read. Microsoft’s documentation on DATEVALUE explains the standard way to turn text dates into real dates, and your regional settings affect how it is interpreted.
The examples below are illustrative exercises, not recorded results from any tool. Sources were checked on 24 October 2026; menus, limits and prices change, so confirm details on the linked pages.
Before you start
- A spreadsheet with messy dates, names or prices (use a copy)
- Twenty sample rows with private data removed
- A decision on whether 03/04 means 3 April or 4 March in your data
Review evidence
Documentation-based review · 2026-10-24
Primary documentation and official guidance checked on 24 October 2026. Examples, checks and exercises are original illustrations, not recorded tool tests. Cover artwork is a conceptual illustration.
Keep the original and list the formats you see
Work on a copy. Leave the original columns untouched and add new columns for the cleaned values. Scan twenty rows and list every format you find for dates, names and prices. Decide how ambiguous dates are read, because that choice changes the data.
Check your result
The original columns are unchanged, and you have a written list of formats and a rule for ambiguous dates.
Ask for a rule or formula, with examples for each format
Give the assistant your list of formats with one example each, your date rule and your spreadsheet program. Ask for formulas, such as a conversion based on DATEVALUE for text dates and trimming and capitalisation functions for names. Ask for the currency symbol and the number in separate columns, and do not convert currencies.
I use [Excel or Google Sheets]. My column A contains dates in these formats: [examples]. Treat ambiguous dates like 03/04/2026 as [day-first or month-first]. Give me a formula to convert column A into real dates in column B, plus a formula that flags rows it cannot convert. Also give formulas to clean names (trim, fix capitalisation, convert “LAST, first” to “First Last”) and to split price into symbol and number. Do not convert currencies. Show a test on these examples: [paste].Example / expected result
Illustrative example; not a recorded model result.
“sometime in April” → the formula returns an error and the row is flagged for manual review.
Check your result
You have formulas that handle every listed format, and each includes a test on your examples.
Test the result against the original
Count blank and error cells in the new columns, compare the number of rows before and after and spot-check twenty rows against the originals. Sum a numeric column before and after cleaning; the totals should match. Fix flagged rows by hand.
Check your result
Row counts and totals match, no unexplained blanks remain and the spot-check passes.
When it goes wrong
Regional settings change how dates are read, so a formula that works on your computer may not work on a colleague’s. Names are hard to standardise fairly; do not “correct” a name you do not understand. Never paste real personal data into a chat.
Make it your own
Clean 30 rows with mixed date formats. Report how many converted automatically, how many were flagged and one value you chose to leave as it was.
Useful next resources
Sources and review
Last checked Oct 24, 2026. Features and account limits can change. Verify important results against your original material.
Did this help you complete your task?
Tell us what needs improving. No account or private output required.
Report incorrect information or a broken linkGenius Forges Editorial
Practical AI lessons with checked sources, labelled examples and human verification.
