Remove leading or trailing spaces in an entire column of data

Question

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 type 0160 using the numeric keypad
• Leave Replace With as blank and select Replace All

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...

Without using a formula you can do this with 'Text to columns'.

• Select the column that has the trailing spaces in the cells.
• Click 'Text to columns' from the 'Data' tab, then choose option 'Fixed width'.
• Set a break line so the longest text will fit. If your largest cell has 100 characters you can set the breakline on 200 or whatever you want.
• Finish the operation.
• You can now delete the new column Excel has created.

The 'side-effect' is that Excel has removed all trailing spaces in the original column.

If it's the same number of characters at the beginning of the cell each time, you can use the text to columns command and select the fixed width option to chop the cell data into two columns. Then just delete the unwanted stuff in the first column.