excel how do - Remove leading or trailing spaces in an entire column of data





3 Answers

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
you excel? from

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.




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.




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:

""

(nothing)






Related