← GCH Services · More free cleanup tips
Disclosure: this article was drafted with AI assistance (Claude) and the formulas were reviewed before publishing. I sell a paid cheat sheet linked at the end.
You exported a CSV and it opens as a mess: names in ALL CAPS, dates as text, phantom spaces, duplicate rows. Here are seven fixes that work in both Excel and Google Sheets.
1. Remove invisible spaces. =TRIM(A2) removes leading/trailing/double spaces. For non-breaking spaces from web exports: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
2. Strip non-printing characters. =CLEAN(A2). Combine: =TRIM(CLEAN(A2)).
3. Fix capitalization. =PROPER(A2) for names, =UPPER() / =LOWER() for codes and emails.
4. Numbers stored as text. If values are left-aligned and SUM returns 0, use =VALUE(A2), or multiply by 1 (=A2*1). Watch for thousands separators from other locales; use SUBSTITUTE first to swap "," for "".
5. Dates stored as text. For ISO text like 2026-09-24: =DATEVALUE(A2) then format the cell as a date. For 24/09/2026 in a US-locale sheet, build it explicitly: =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)).
6. Split one column into two. Excel: Data > Text to Columns. Google Sheets: Data > Split text to columns, or =SPLIT(A2," "). Excel 365 also has =TEXTSPLIT(A2," ").
7. Remove duplicates safely. Excel: Data > Remove Duplicates. Sheets: Data > Data cleanup > Remove duplicates. Always work on a copy of the raw sheet, and clean a helper column (steps 1-3) first, otherwise "Smith " and "Smith" count as different rows.
Workflow tip: keep the raw export on one tab, do all fixes with formulas on a second tab, then copy > paste values only into a third "final" tab. You can redo the process when the next export arrives. Clean each file this way before you merge spreadsheets, so keys match.
If you'd rather not remember all of these, I put the formulas, a checklist and common gotchas on one page: the "CSV & Excel Data Cleanup Cheat Sheet" ($7). GCH Services is the seller (we made it and we get the money).
Buy on Gumroad · Pay by card (Stripe)
Also available as a free download on itch.io.
Sources: reader questions seen repeatedly on r/excel and r/googlesheets; formulas are standard Excel/Sheets functions. Test on a copy of your data first.