TRIM removes extra ordinary spaces. Replace nonbreaking spaces with ordinary spaces first when the text was copied from a web page.
Visual walkthrough
Excel›B2›TRIM and SUBSTITUTE
Before
A2 contains extra spaces around and between words.
After
B2 contains clean text with single spaces.
Check the result
Compare LEN before and after, then test the lookup again.
Step-by-step instructions
- Place the original text in column A and keep a backup.
- Enter the formula below in B2.
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")) - Copy the formula down and compare several cleaned values with their originals.
- If you need fixed text, copy the cleaned results and use Paste Special → Values in the intended destination.
Frequently asked questions
Will TRIM remove all spaces between words?
No. It reduces repeated ordinary spaces to a single space and removes ordinary spaces from the start and end.
Reference: Microsoft Support