Remove leading or trailing spaces in an entire column of data
If you would like to use a formula, the TRIM
function will do exactly what you're looking for:
+----+------------+---------------------+
| | A | B |
+----+------------+---------------------+
| 1 | =TRIM(B1) | value to trim here |
+----+------------+---------------------+
So to do the whole column...
1) Insert a column
2) Insert TRIM
function pointed at cell you are trying to correct.
3) Copy formula down the page
4) Copy inserted column
5) Paste as "Values"
Should be good to go from there...
Quite often the issue is a non-breaking space - CHAR(160)
- especially from Web text sources -that CLEAN
can't remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
Ron de Bruin has an excellent post on tips for cleaning data here
You can also remove the CHAR(160)
directly without a workaround formula by
- Edit .... Replace your selected data,
- in Find What hold
ALT
and type0160
using the numeric keypad - Leave Replace With as blank and select Replace All