↓Skip to main content
  1. Teches/

删除 Excel 单元格中的前导和尾随空格

··1 min
Table of Contents

由于平常很少真的去处理表格文档，所以有次领导把一个有问题的表格让我去解决时，花了不少时间才明白是导入文本时产生的空格和非打印字符导致。
怎样来删除 Excel 单元格中的前导和尾随空格呢？下文记录一些方法。更多对工作表进行大扫除的示例，可以参阅微软官方帮助页清理数据的十大方法。

一、函数法 #

在数据列后面插入一个辅助列，输入公式“=TRIM(B2)”后下拉填充完成，再将辅助列数据复制后选择性粘贴为【数值】即可。

TRIM 函数
除了单词之间的单个空格之外，移除文本中的所有空格。 对于从另一个可能含有不规则间距的应用程序收到的文本，可以使用 TRIM。
也就是说可以移除前导空格和尾随空格，也可以将单词间的多个空格删减为一个。
语法： TRIM(参数)
参数可以是单元格引用，也可以是文本字符串，要注意文本必须包含在双引号中。

重要: TRIM 函数专用于剪裁文本中的 7 位 ASCII 空格字符（值 32）。 在 Unicode 字符集中，存在名为不间断空格字符的附加空格字符，其十进制值为 160。 通常在网页中使用此字符作为 HTML 实体   空格。 TRIM 函数本身不会删除此不间断空格字符。

二、查找替换法 #

选中数据单元格区域，按下 Ctrl+H，在【查找内容】中输入空格，然后点击【全部替换】即可。

去除不可见字符 #

发现数据不能处理时的检查 #

先用 LEN 函数来计算一下长度，有些看上去相同字符长度而实际却不同；
确认是不是空格：选中单元格，在编辑栏中按Ctrl+Shift+下箭头，多出来的部分就是空格，用替换法或 TRIM 函数就可以处理。
若没有空格，又在计算长度时占据了 1 个字符，但在编辑栏中却不能发现的字符就叫“不可见字符”或“不可打印字符”，主要是不同系统的数据源转换时带来的。

CLEAN 函数 #

删除文本中所有不能打印的字符。 对从其他应用程序导入的文本使用 CLEAN，将删除其中含有的当前操作系统无法打印的字符。
语法： CLEAN(参数)

Clean 函数可把文本前后所有非打印字符都删除，但不会删除文字之间的所有非打印字符，而是留下一个。Clean 函数与 Trim 函数的作用相似，所不同的是 Trim 函数用于去除空格，CLEAN 函数主要用来删除换行的。
必要时可结合使用：=TRIM(CLEAN(A1))

重要: CLEAN 函数用于删除文本中 7 位 ASCII 码的前 32 个非打印字符（值为 0 到 31）。 在 Unicode 字符集中，有附加的非打印字符（值为 127、129、141、143、144 和 157），CLEAN 函数自身不删除这些附加的非打印字符，

另一种特殊空格可以在编辑框选中它，但 TRIM 和 CLEAN 两个函数都不能清除。这个空格是 UNICODE 编码为 160 的字符，名为“不间断空格字符”，简称 NBSP，在网页中经常使用。
清除最简单的方法是在编辑栏选择这个字符，然后在替换对话框中将它替换为空，或者公式 =SUBSTITUTE(B2,UNICHAR(160),"") 即将 B2 中 UNICHAR(160) 替换为空。

SUBSTITUTE 函数 #

在某一文本字符串中替换指定的文本，如果需要在某一文本字符串中替换特定位置处的任意文本使用函数 REPLACE。
语法： SUBSTITUTE(text, old_text, new_text, [instance_num])

  • text 数据区域， 需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。
  • old_text 需要替换的文本。
  • new_text 用于替换 old_text 的文本。
  • Instance_num 可选。 指定要将第几个 old_text 替换为 new_text。 如果指定了 instance_num，则只有满足要求的 old_text 被替换。 否则，文本中出现的所有 old_text 都会更改为 new_text。

Clean 函数的扩展应用实例 #

Sum + Clean 函数组合实现数值带非打印字符求和

假如要对数值为文本且前面带非打印字符的列求和，双击 E9 单元格，把公式 =SUM(E2:E8) 复制到 E9，按回车，返回 0；再次双击 E9，把公式改为 =SUM(VALUE(CLEAN(E2:E8)))，按 Ctrl + Shift + 回车，返回求和结果 5150；

公式 =SUM(VALUE(CLEAN(E2:E8))) 说明：
A、公式 =SUM(E2:E8) 之所以返回 0，是因为 E2:E8 中的数值前面有非打印字符，计算时，Sum 函数不能把它们转为数值型。
B、公式 =SUM(VALUE(CLEAN(E2:E8))) 为数组公式，而数组公式都需要按 Ctrl + Shift + 回车才能返回计算结果。
C、E2:E8 以数组形式返回 E2 至 E8 中的所有数值，接着，Clean 函数把每个数值前面的非打印字符都删除，最后返回数组{“892”;“762”;“760”;“982”;“329”;“528”;“897”}。
D、则公式变为 =SUM(VALUE({“892”;“762”;“760”;“982”;“329”;“528”;“897”}))，进一步计算，Value 函数把数组中的所有元素都由文本转为数值。
E、则公式变为 =SUM({892;762;760;982;329;528;897})，最后再用 Sum 函数对数组求和。
提示：如果在 E9 中，出现求和一次后不能再求和，需要把 E9 的单元格格式设置为“数值”，按 Ctrl + 1（需关闭中文输入法），打开“设置单元格格式”窗口，再选择“数字”选项卡，最后选择左边“数值”即可。