Chapter 10 · intermediate · 6 min
Cleaning: TRIM, PROPER, SUBSTITUTE, VALUE
Real spreadsheet data rarely arrives ready to compute on — extra spaces from copy-pasting, inconsistent casing, numbers stuck inside currency strings. Excel's cleaning functions fix these one problem at a time, and the fixes nest: the output of one becomes the input of the next.
We'll clean a small customer sheet throughout: five names typed five different ways, and five prices that were meant to be numbers but arrived as text.
The grid
Column A holds customer names with stray spaces and mixed casing. Column D holds prices as text, each with a currency label — not always spelled the same way. Columns B, C, E and F are where the cleaned versions go.
TRIM: collapse extra spaces
=TRIM(A2)TRIM removes leading and trailing spaces and collapses any run of multiple spaces between words down to one. It does not touch casing — "aiman rahman" stays lowercase.
Stray spaces are the most common dirty-data problem precisely because you can't see them: "Lim Ah Kow " and "Lim Ah Kow" look identical in a cell, but a lookup treats them as two different customers.
A2 is " aiman rahman " — leading spaces, three spaces between words, trailing spaces. What does TRIM leave?
Scroll to see all 6 columns →
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name (raw) | Trimmed | Name | Price (raw) | No label | Price |
| 2 | aiman rahman | =TRIM(A2) | RM 1,200 | |||
| 3 | SITI NURHALIZA | RM850 | ||||
| 4 | tan wei ming | rm 2,000 | ||||
| 5 | priya devi | RM 99 | ||||
| 6 | Lim Ah Kow | RM 12,500 |
PROPER: fix the casing
PROPER capitalizes the first letter of each word and lowercases the rest — so it fixes an all-lowercase name and an all-caps one alike. Nest it around TRIM so casing and spacing are fixed together in one formula:
=PROPER(TRIM(A3))The nesting order matters for reading, not for the result: TRIM runs first because it's innermost, and PROPER works on whatever TRIM hands it.
A3 is "SITI NURHALIZA", all caps. What does PROPER produce?
Scroll to see all 6 columns →
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name (raw) | Trimmed | Name | Price (raw) | No label | Price |
| 2 | aiman rahman | RM 1,200 | ||||
| 3 | SITI NURHALIZA | =PROPER(TRIM(A3)) | RM850 | |||
| 4 | tan wei ming | rm 2,000 | ||||
| 5 | priya devi | RM 99 | ||||
| 6 | Lim Ah Kow | RM 12,500 |
SUBSTITUTE: replace specific text
SUBSTITUTE(text, old, new) replaces every occurrence of old inside text with new. It's exact-text matching, not a pattern — useful for stripping a known label:
=SUBSTITUTE(D2,"RM ","")D2 is "RM 1,200" — this strips the "RM " prefix, leaving "1,200" as text. The comma is still there; SUBSTITUTE only removed what you told it to. Notice it sits on the left of its cell after you run it: Excel left-aligns text and right-aligns numbers, and "1,200" is still text.
D2 is "RM 1,200". What's left after removing "RM "?
Scroll to see all 6 columns →
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name (raw) | Trimmed | Name | Price (raw) | No label | Price |
| 2 | aiman rahman | RM 1,200 | =SUBSTITUTE(D2,"RM ","") | |||
| 3 | SITI NURHALIZA | RM850 | ||||
| 4 | tan wei ming | rm 2,000 | ||||
| 5 | priya devi | RM 99 | ||||
| 6 | Lim Ah Kow | RM 12,500 |
VALUE: text back into a number
Even a cleaned-up string like "1,200" is still text — VALUE converts a numeric-looking string into an actual number Excel can do arithmetic on. Chain it with SUBSTITUTE (once per thing that needs removing) to go straight from raw text to a usable number:
=VALUE(SUBSTITUTE(SUBSTITUTE(D2,"RM ",""),",",""))The inner SUBSTITUTE strips "RM ", the outer one strips the comma, and VALUE converts what's left. The breakdown under the grid shows the text shrinking one step at a time — and the result is the first value in this chapter to sit on the right of its cell.
After stripping "RM " and the comma from "RM 1,200", what number does VALUE return?
Scroll to see all 6 columns →
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name (raw) | Trimmed | Name | Price (raw) | No label | Price |
| 2 | aiman rahman | RM 1,200 | =VALUE(SUBSTITUTE(SUBSTITUTE(D2,"RM ",""),",","")) | |||
| 3 | SITI NURHALIZA | RM850 | ||||
| 4 | tan wei ming | rm 2,000 | ||||
| 5 | priya devi | RM 99 | ||||
| 6 | Lim Ah Kow | RM 12,500 |
How Excel works it out · run it to see each value
- 1SUBSTITUTE(D2,"RM ","")=?label gone
- 2SUBSTITUTE(SUBSTITUTE(D2,"RM ",""),",","")=?comma gone
- 3VALUE(SUBSTITUTE(SUBSTITUTE(D2,"RM ",""),",",""))=?now a number
The trap: SUBSTITUTE matches exactly
That formula is right for row 2 and wrong for two of the other four. Copy it down and D3's "RM850" comes back #VALUE! — there's no space after RM, so "RM " never matched and VALUE choked on the letters. D4's "rm 2,000" fails the same way for a different reason: SUBSTITUTE is case-sensitive, and "rm" isn't "RM".
The fix is to make the text predictable before you substitute into it. UPPER turns "rm" into "RM"; then strip plain "RM" with no space, and let VALUE shrug off the leading space that's left behind:
=VALUE(SUBSTITUTE(SUBSTITUTE(UPPER(D4),"RM",""),",",""))This time the challenge sits on row 4, the awkward one. Get it right there, then fill it up and down: all five prices should come out as numbers, right-aligned.
Row 4 as a real number, whatever the label's case
In F4, turn D4's "rm 2,000" into a real number — in a way that would also survive D3's "RM850". Then fill it to the other rows.
Scroll to see all 6 columns →
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name (raw) | Trimmed | Name | Price (raw) | No label | Price |
| 2 | aiman rahman | RM 1,200 | ||||
| 3 | SITI NURHALIZA | RM850 | ||||
| 4 | tan wei ming | rm 2,000 | ||||
| 5 | priya devi | RM 99 | ||||
| 6 | Lim Ah Kow | RM 12,500 |