Every messy spreadsheet I've fixed this year died the same way: someone pasted PDF tables (or website exports, or phone-formatted lists) into cells and called it data. Numbers became text, dates became strings, and every SUM below them silently reported zero.
Here's the cleanup checklist I run before trusting any imported sheet.
1. Check what Excel actually stored
Select the column and look at the default alignment: numbers right-align, text left-aligns. If your "amounts" sit on the left, they're text. The fastest audit:
=ISTEXT(A2)
Or one click: F5 → Special → Text. Everything that lights up won't sum.
2. Strip the invisible junk
PDF extraction loves non-breaking spaces (char 160), stray tabs, and zero-width characters. They break VLOOKUP even when the values look identical:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
Run it over the whole column, paste-special as values.
3. Force the type, don't hope for it
For numbers stored as text with thousands separators or currency symbols, the reliable one-pass fix is Text to Columns (Data tab) → Delimited → Finish. Excel re-parses every cell and coerces types. For stubborn European formats (1.234,56), use:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",","."))
4. Dates: pick ONE format and convert
Mixed formats (12 March vs March 12 vs 2026-03-12) sort wrong and compare wrong. Normalize with DATEVALUE where possible, or split-and-rebuild with TEXT/LEFT/MID when not. Then set the column format explicitly — never rely on the regional default.
The honest part: I do this cleanup professionally (it's literally my day job), and I record 60-second versions of these fixes as videos on my YouTube channel. If you'd rather not fix the file yourself, I take cleanup and rebuild jobs on my Kwork profile — and I publish my template library as free web apps you can fork. Disclosure: those are my links.
What's the worst data format someone has handed you? Mine: a scanned fax of a dot-matrix printout.
Top comments (0)