Course outline / Data cleaning
Lesson 55 of 75 · Intermediate · 5 exercises
Cleaning data with formulas
Fix stray spaces, odd capitals and numbers stored as text.
What you'll practise
- Remove stray spaces with TRIM
- Tidy capitals too
- Turn text into a number
- Standardise codes
- Check your cleaning
Remove stray spaces with TRIM
Data exported from other systems is often messy. Extra spaces you can't see stop matches and lookups working. TRIM removes spaces at the start and end, and squeezes double spaces down to one.
In cell E2, use TRIM to clean the company name in A2.
Tidy capitals too
Cleaning often takes more than one step, so functions get nested. PROPER capitalises each word, and putting TRIM inside it cleans the spacing first.
=PROPER(TRIM(text))
In cell E2, clean A2 by nesting TRIM inside PROPER.
Turn text into a number
B2 looks like a number, but it's text because of the comma, so it won't add up. First use SUBSTITUTE to remove the comma, then VALUE to turn the result into a real number.
=VALUE(SUBSTITUTE(text, ",", ""))
In cell E2, turn the amount in B2 into a number.
Standardise codes
Consistent codes make data easier to group and filter. Country codes are usually written in capitals.
In cell E2, use UPPER to standardise the country code in C2.
Check your cleaning
Combining functions lets you check your cleaning worked. LEN counts characters, and running it on the trimmed text shows the true length.
In cell E2, use LEN and TRIM together to count the characters in A2 after cleaning.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.