[excel] Remove leading or trailing spaces in an entire column of data
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
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
0160using the numeric keypad
- Leave Replace With as blank and select Replace All
How do I remove leading or trailing spaces of all cells in an entire column?
The worksheet's conventional
Find and Replace (aka Ctrl+H) dialog is not solving the problem.
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'. 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.
I was able to use Find & Replace with the "Find what:" input field set to:
" * "
(space asterisk space with no double-quotes)
and "Replace with:" set to: