Chapter 10 · intermediate · 5 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 one messy name and one messy currency string throughout.

The grid

A1 holds a name with extra whitespace and inconsistent casing. D1 holds a price as text, prefixed with a currency label and a thousands separator.

TRIM: collapse extra spaces

=TRIM(A1)

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.

A1 is " aiman rahman " — leading spaces, three spaces between words, trailing spaces. What does TRIM leave?

Scroll to see all 9 columns →

	A	B	C	D	E	F	G	H	I
1	aiman rahman	=TRIM(A1)		RM 1,200

Editable, try changing it

PROPER: fix the casing

PROPER capitalizes the first letter of each word. Nest it around TRIM so casing and spacing are fixed together in one formula:

=PROPER(TRIM(A1))

Starting from the trimmed "aiman rahman", what does PROPER produce?

Scroll to see all 9 columns →

	A	B	C	D	E	F	G	H	I
1	aiman rahman		=PROPER(TRIM(A1))	RM 1,200

Editable, try changing it

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(D1,"RM ","")

D1 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.

D1 is "RM 1,200". What's left after removing "RM "?

Scroll to see all 9 columns →

	A	B	C	D	E	F	G	H	I
1	aiman rahman			RM 1,200	=SUBSTITUTE(D1,"RM ","")

Editable, try changing it

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(D1,"RM ",""),",",""))

The inner SUBSTITUTE strips "RM ", the outer one strips the comma, and VALUE converts what's left.

A real number, ready for arithmetic

In I1, turn D1's "RM 1,200" into a real number Excel can do arithmetic on.

Scroll to see all 9 columns →

	A	B	C	D	E	F	G	H	I
1	aiman rahman			RM 1,200