Remove leading or trailing spaces in an entire column of data

venkat picture venkat · Mar 6, 2012 · Viewed 935.7k times · Source

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.


brettdj picture brettdj · Mar 6, 2012

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