Stage 2 · Lesson 4 of 6
Cleaning text, numbers and dates
Real exports are messy. Names come in mixed case, cells have trailing spaces, numbers are stored as text, dates arrive in several formats, and codes need splitting. Spreadsheet functions fix most of this.
removes extra spaces, PROPER fixes capitals and SUBSTITUTE swaps characters. VALUE and DATEVALUE turn text into numbers and dates, and TEXT, LEFT and SPLIT reshape text. Remove Duplicates and Find and Replace handle the rest.
Clean in next to the raw data, not on top of it, so anyone can see what changed. Newer functions such as help you inspect the result. List the distinct values of a column and you will spot "Lahore", "lahore" and "LHR" straight away.
Common trapcleaning by hand, which cannot be repeated next month and cannot be checked by anyone else.
A question to check yourself, an AI practice session and 2 cheat sheet entries come with the full roadmap. Unlock
Resources for this stage
Start with
- GuideFreeExcel help and learning
Microsoft · Beginner
Official, accurate guides to XLOOKUP, SUMIFS, PivotTables and cleaning functions, with examples you can paste