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.

ABCD
1Full nameEmailProduct codeCity
2Ana Lopez[email protected]US-4417-RED new york
3Ben Ortiz[email protected]CA-0932-BLUTORONTO
4Cara Smith[email protected]US-1205-GRNchicago
5Dev Patel[email protected]UK-7781-BLKLondon
6Eva Ross[email protected]US-3390-REDsan 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.

H2

2 Return the first 3 letters of the name in A3, in capitals.

H2

3 Return each product code without dashes (like US4417RED). Copied down to H6.

H2

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 pricing

Common TRIM, PROPER & SUBSTITUTE mistakes

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.

Keep practicing