Why does TRIM leave some spaces?

They are non-breaking spaces (CHAR(160)), which TRIM does not know about.

The TRIM function

Removing extra spaces: at the start, at the end, and doubled spaces in between. The first thing to do when a lookup cannot find what is clearly there.

Syntax

=TRIM(text)

In a French Excel: =SUPPRESPACE(texte).

Arguments

text: the cell to clean.

An example

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Non-breaking spaces become spaces, invisible characters are removed, then extra spaces.

Pitfalls

Non-breaking spaces (frequent in web exports) are not removed: SUBSTITUTE first.
The result is a formula: to clean the column itself, copy and paste values.

Going further

SUBSTITUTE.
Clean text: a whole file, non-breaking and invisible characters included, with no formula.

Frequently asked questions

Why does TRIM leave some spaces?: They are non-breaking spaces (CHAR(160)), which TRIM does not know about.