excel - 删除整列数据中的前导或尾随空格(Remove leading or trailing spaces in an entire column of data)

Question

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.

(工作表的常规Find and Replace （也称为Ctrl + H ）对话框无法解决问题。)

  ask by venkat translate from so

Answer 1

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

(通常问题是一个不间断的空间 - CHAR(160) - 特别是来自Web文本源 - CLEAN无法删除，所以我会更进一步，尝试这样的公式，取代任何不破坏的标准空间)

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Ron de Bruin has an excellent post on tips for cleaning data here

(Ron de Bruin在这里有关于清理数据的提示)

You can also remove the CHAR(160) directly without a workaround formula by

(您也可以直接删除CHAR(160)而不使用解决方法)

• Edit .... Replace your selected data,

  (编辑....替换您选择的数据，)

• in Find What hold ALT and type 0160 using the numeric keypad

  (在Find What hold ALT并使用数字键盘输入0160)

• Leave Replace With as blank and select Replace All

  (将Replace With留空并选择全部替换)