Course outline / Data cleaning
Lesson 56 of 75 · Intermediate · 4 exercises
Cleaning messy text
Remove hidden characters and extra spaces, fix capitals, and compare text exactly.
What you'll practise
- Remove hidden characters with CLEAN
- CLEAN and TRIM together
- Fix the capitals as well
- Compare text exactly with EXACT
Remove hidden characters with CLEAN
Data copied from other systems can carry invisible characters, such as tabs and line breaks, that you can't see but that break lookups. CLEAN removes them.
In cell D2, use CLEAN on A2.
CLEAN and TRIM together
TRIM removes spaces at the start and end and squeezes runs of spaces between words down to one. Combined with CLEAN, it tidies most imported text.
In cell D2, apply TRIM to CLEAN(A2).
Fix the capitals as well
In cell D2, wrap TRIM(CLEAN(A2)) in PROPER, so each word starts with a capital.
Compare text exactly with EXACT
An ordinary comparison such as =B2=B3 ignores capitals, so "ab-12" equals "AB-12". EXACT is TRUE only when the text matches exactly, capitals included.
In cell D2, use EXACT to compare B2 with B3.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.