How to remove extra spaces in Excel
Imported text often carries invisible spaces at the start, at the end or between words. They break lookups and make duplicates look different.
TRIM removes spaces at both ends and turns runs of spaces between words into a single space.
The formula
| A | |
|---|---|
| 1 | Name |
| 2 | Amara Okafor |
=TRIM(A2)Result: Amara Okafor
The spaces before, after and between the names are tidied up.
Step by step
- In an empty column, type =TRIM(A2).
- Fill down.
- To replace the original, copy the new column and use Paste Special > Values over it.
Other ways to write it
=PROPER(TRIM(A2))Fixes capitals at the same time, as in Amara Okafor.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Non-breaking spaces
- Text copied from web pages can contain a different kind of space that TRIM ignores. Replace it first with SUBSTITUTE(A2,CHAR(160)," ").
- Leaving the formulas in place
- If you delete the original column, the TRIM formulas break. Paste them as values first.
Reading isn’t the same as doing
Cleaning messy text gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.