TRIM removes spaces from the start and end of a piece of text, and squeezes any run of spaces in the middle down to one.
Syntax
TRIM(text)
When to use it
After any paste from a website, a PDF or an exported report. It is the first thing to try when two values look identical but Excel insists they are not.
What trips people up
TRIM does not touch the non-breaking space that copying from a web page often leaves behind. That one needs CLEAN, or a find and replace.
Text copied from a web page often contains non-breaking spaces, which TRIM leaves alone. Turn them into ordinary spaces first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Does TRIM remove the spaces between words?
No. It removes every space at the start and end, and cuts any run of spaces between words down to one.
How do I replace the original column with the cleaned text?
Copy the column of TRIM results, paste it over the original with Paste Special, Values, then delete the helper column.
Related functions
SUBSTITUTE — SUBSTITUTE swaps one piece of text for another wherever it appears.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
