Remove Extra Spaces
Free Remove Extra Spaces: use =TRIM(A2) to remove leading, trailing and repeated normal spaces, and combine it with SUBSTITUTE for non-breaking spaces...
14 characters removed · 25 characters
Non-breaking spaces from web copy-paste are normalised too — TRIM alone leaves those behind.
How it works
In Excel or Google Sheets, use =TRIM(A2) to remove leading, trailing and repeated normal spaces, and combine it with SUBSTITUTE for non-breaking spaces from web data.
Add a helper column beside the messy text.
Enter =TRIM(A2) for normal spaces.
Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) for web-import spaces.
Copy the cleaned results and Paste Values over the original if needed.
What this does
TRIM cleans normal spaces around and between words. Data copied from websites can contain non-breaking spaces, which TRIM alone may not remove. In that case, SUBSTITUTE(A2,CHAR(160)," ") replaces non-breaking spaces before TRIM cleans the result.
A worked example
A2 contains " ACME West ". =TRIM(A2) returns "ACME West". If imported text still refuses to match, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). Space cleanup fixes failed lookups, duplicate detection and joins where values look identical but do not match.
Common mistakes
- Using Find/Replace space with nothing and removing intentional spaces inside names.
- Forgetting non-breaking spaces.
- Replacing formulas before checking results.
- Cleaning only one lookup column when both sides need
TRIM.
FAQ
What does TRIM remove?
Leading, trailing and repeated normal spaces.
Why are spaces still there?
They may be non-breaking spaces, character 160.
Can I remove all spaces?
Use SUBSTITUTE(A2," ",""), but only when internal spaces are truly unwanted.
Built in your browser when you click. Nothing is uploaded, no signup, free for commercial use.