Cleaning Messy Data: Names, Dates and Duplicates

Data from other systems, colleagues or customers is almost never clean. Extra spaces, mixed capitals, dates stored as text and duplicate rows break formulas and give wrong totals. Cleaning used to take hours. With AI telling you which formula or tool to use, it takes minutes.

Always start with a copy

Never clean the original data in place. Work on a copy of the sheet, or put cleaned values in new columns next to the old ones. When everything checks out, you can replace the original.

Describe the mess

Paste a few messy rows (with dummy names) and say what you want:

Excel for Microsoft 365. Column B has customer names like these:
"  sharma traders", "PATEL ELECTRICALS ", "gupta & sons"
I want them trimmed and in proper case: "Sharma Traders".
Give me a formula for C2 that I can copy down.

The answer will be something like:

=PROPER(TRIM(B2))

TRIM removes extra spaces and PROPER capitalises each word. Copy C2 down, then check a few rows by eye. Note that PROPER can produce things like "Abc Pvt. Ltd" from "ABC PVT. LTD", so fix short forms by hand or ask the AI for a way to keep them.

Common clean-ups and what to ask for

Dates stored as text

Text dates are the most common problem in Indian spreadsheets, because systems mix DD-MM-YYYY and MM-DD-YYYY. A quick test: real dates align to the right of the cell by default; text aligns to the left.

Column A has dates as text like "05-11-2026", meaning 5 November 2026
(DD-MM-YYYY). My Windows date format may be different. Give me a formula
that converts them to real Excel dates regardless of my system settings.

A safe answer builds the date from its parts, so your system settings do not matter:

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

Then format the new column as a date and check a few rows where the day is 12 or less, because those are the ones most likely to be swapped.

Flash Fill: let Excel copy your pattern

For splitting or combining text, Excel's Flash Fill is often faster than any formula:

1. Next to "Sharma, Rahul", type "Rahul Sharma" yourself.
2. Go to the cell below and press Ctrl+E.
3. Excel fills the rest following your pattern. Check the results.

Ask the AI "Can Flash Fill do this?" before writing a complex formula.

Duplicates: decide what "duplicate" means

Before removing duplicates, decide which columns define a duplicate: the whole row, or only Invoice Number? Ask:

I have 2,000 rows of invoices. Some invoices appear twice with the same
Invoice No but different Remarks. How do I find them first, before I
delete anything?

The AI will suggest marking them first, for example with =COUNTIF($A:$A, A2)>1 and a filter, so you can review before deleting. Remove Duplicates cannot be undone once you save and close the file.

Check after cleaning

• Row count before and after (should only change if you removed duplicates on purpose)
• Totals of number columns before and after
• A few random rows compared with the original

Practice task

Take a messy export from any system you use, or make one with 20 rows full of extra spaces, mixed capitals and text dates. Clean it using AI-suggested formulas in new columns, then check row counts and totals against the original.

Key takeaways

• Always clean in a copy or in new columns, never in the original.
• TRIM, PROPER, CLEAN, VALUE and Flash Fill solve most clean-ups.
• Convert text dates by building them with DATE from their parts.
• Mark duplicates and review them before deleting, then compare counts and totals.