Stray spaces are the quiet reason a lookup or a match fails. TRIM clears the doubled-up and trailing spaces while leaving the single ones between words alone.
The formula
=TRIM(A2) in column B
What each part does
TRIM
Removes spaces from the start and end, and reduces any run of spaces in the middle to one. It leaves the real words alone.
A2
The cell to clean. Point it at wherever your messy text is.
How it works, and what to watch for
TRIM tidies ordinary spaces, but data pasted from the web often carries a non-breaking space that it cannot see. For those, SUBSTITUTE swaps CHAR(160) for a normal space first.
This is worth trying the moment a lookup returns #N/A on a value you can clearly see. Nine times out of ten it is a hidden space.
The result is fresh text, so if you want to replace the originals, paste the tidied column back as values.
Questions
My lookup still fails after TRIM, why?
The space is probably a non-breaking one from a web page. Clear it with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Does TRIM remove all the spaces?
No, it keeps single spaces between words and only clears the extra ones, along with any at the start or end. That is usually exactly what you want.
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.
