TRIM, PROPER & SUBSTITUTE practice exercises
Imported data is messy: extra spaces, random capitals, unwanted characters. TRIM removes extra spaces, PROPER/UPPER/LOWER fix capitalization, and SUBSTITUTE swaps one piece of text for another. Clean text is what makes lookups and counts work.
Syntax
TRIM(text) · PROPER(text) · UPPER(text) · LOWER(text) · SUBSTITUTE(text, old, new)
TRIM- Removes spaces at the start and end, and turns double spaces inside into single ones.
PROPER / UPPER / LOWER- Capitalizes each word / everything / nothing.
SUBSTITUTE(text, old, new)- Replaces every occurrence of old with new. Use "" as new to delete.
The practice data
Customer records with full names, emails, product codes (country-number-color) and messy city names. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | Product code | City | |
| 2 | Ana Lopez | [email protected] | US-4417-RED | new york |
| 3 | Ben Ortiz | [email protected] | CA-0932-BLU | TORONTO |
| 4 | Cara Smith | [email protected] | US-1205-GRN | chicago |
| 5 | Dev Patel | [email protected] | UK-7781-BLK | London |
| 6 | Eva Ross | [email protected] | US-3390-RED | san diego |
Worked example
=TRIM(D2)
Removes the extra spaces around "new york". Result:
| new york |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return each City with extra spaces removed and in Proper Case (like New York). Copied down to H6.
2 Return the first 3 letters of the name in A3, in capitals.
3 Return each product code without dashes (like US4417RED). Copied down to H6.
Master TRIM, PROPER & SUBSTITUTE in the full course
Lesson 7 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon TRIM, PROPER & SUBSTITUTE mistakes
- Cleaning by hand instead of with a formula, so new rows arrive messy again.
- Forgetting non-breaking spaces from web pages, which TRIM doesn't remove. Use SUBSTITUTE(A2,CHAR(160)," ") first.
- PROPER on names like "McDonald" or "O'Neil", which may need a manual fix.
FAQ
Why does my VLOOKUP fail on values that look the same?
Usually hidden spaces. TRIM both the lookup value and the table column.
How do I remove all spaces?
=SUBSTITUTE(A2," ","") removes every space, not just extra ones.