TRIM pitfall 1: Removes ASCII spaces (and runs of them between words) but does NOT remove non-breaking spaces (CHAR(160)) or tab characters?

Removes ASCII spaces (and runs of them between words) but does NOT remove non-breaking spaces (`CHAR(160)`) or tab characters. Use `CLEAN(SUBSTITUTE(x, CHAR(160), " "))` for stubborn whitespace.

TRIM pitfall 2: Collapses internal runs of spaces to single spaces: that's a feature, not a bug, but it WILL alter content like ASCII art or aligned columns?

Collapses internal runs of spaces to single spaces: that's a feature, not a bug, but it WILL alter content like ASCII art or aligned columns.

Home/Formulas/Text/TRIM

Texteasy

TRIM()

Removes leading, trailing, and repeated spaces in text.

Syntax

=TRIM(text)

Try it

TRIM("  Acme   Co  ")
TRIM("  Acme   Co  ")

→

Acme Co

Sample data · sales.csv

	A	B	C	D
1	Company	Region	Quarter	Revenue
2	Acme Co	West	Q1	18,400
3	Acme Co	West	Q2	22,100
4	Acme Co	West	Q3	31,700
5	Acme Co	West	Q4	48,200
6	Globex	East	Q1	14,200
7	Globex	East	Q2	19,800
8	Globex	East	Q3	23,400

Company

Region

Quarter

Revenue

Acme Co

West

18,400

Acme Co

West

22,100

Acme Co

West

31,700

Acme Co

West

48,200

Globex

East

14,200

Globex

East

19,800

Globex

East

23,400

Common pitfalls

Removes ASCII spaces (and runs of them between words) but does NOT remove non-breaking spaces (CHAR(160)) or tab characters. Use CLEAN(SUBSTITUTE(x, CHAR(160), " ")) for stubborn whitespace.
Collapses internal runs of spaces to single spaces: that's a feature, not a bug, but it WILL alter content like ASCII art or aligned columns.

Related formulas

SUBSTITUTE()

Replaces existing text with new text in a string. Match by content.

Used by

SUBSTITUTE()

Replaces existing text with new text in a string. Match by content.

More Text formulas →Keyboard shortcuts for Sheets →