Excel Clean函数的使用方法5个实例，含删除文字前后及之间的非打印字符和Clean无法删除的空格

网友投稿 · 2023-09-01 18:09 · 云表格 · 阅读 705

在 Excel 中，Clean函数用于删除文本中所有不能打印的字符。如果这些字符在文本的前后，则可以把它们全部删除；如果这些字符在文字之间，不会删除全部，而是留下一个。Clean函数与Trim函数的作用相似，所不同的是Trim函数用于去除空格。

Clean函数只能删除 ASCII 码为 0 到 31 的非打印字符，一些 Unicode 字符集的非打印字符它无法删除，这种情况需要用Substitute函数替换；如果仍然无法删除，还需要结合 Left、Mid、Right、Code 等函数才能删除。另外，Clean 的文本参数可以为数组，此功能可以把一列中的非打印字符一次全部删除；如果求和数值带非打印字符，就需要先用Clean把它们去除再求和。

一、Excel Clean函数的语法

1、表达式：ClEAN(Text)

2、说明：

A、Clean函数用于删除文本中所有非打印字符，这些非打印字符是指 ASCII 码表中编码为 0 - 31 的字符，它们有些是空格有些不是，具体请查找《ASCII码表》一文。

B、除 ASCII 码表中的非打印字符外，Unicode 字符集中也有非打印字符，它们的值为 127、129、141、143、144 和 157，Clean函数无法删除它们，若要删除，需要用Substitute函数。

C、Text 为要删除非打印字符的文本；Clean函数可把文本前后所有非打印字符都删除，也可删除文字之间的非打印字符，但不会删除文字之间的所有非打印字符，还会留下一个，这与用于去掉空格的Trim函数相同。

二、Excel Clean函数的使用方法及实例

（一）删除文本前后所有非打印字符实例

1、假如要删除文本“" Excel 函数教程 "”前后的所有非打印字符。双击 A1 单元格，把公式 =CHAR(9)&"Excel 函数教程"&CHAR(11) 复制到 A1，选中 B1，输入公式 =CLEAN(A1)，按回车，返回“Excel 函数教程”；框选 A1:B1，按 Ctrl + C 复制，切换到一个空文本窗口，按 Ctrl + V 粘贴，A1 中的文本前后所有非打印字符都被删除；选中“" Excel 函数教程 "”，把它复制到 A2，在 B2 输入公式 =CLEAN(A2)，按回车，也返回“Excel 函数教程”；操作过程步骤，如图1所示：

图1

2、说明：

A、CHAR(9) 返回“水平制表符”，9 是水平制表符的 ASCII 码；CHAR(11) 返回“垂直制表符”，11 是垂直制表符的 ASCII 码。

B、从以上演示可知，无论是用Char函数返回的非打印字符，还是从文本中复制到单元格的非打印字符，Clean函数都能把它们删除。

（二）删除文字之间非打印字符实例

1、假如要把文本“Excel函数使用教程”中的非打印空格删除。在文本窗口，选中要删除空格的文本，按 Ctrl + C 复制，切换到 Excel 窗口，双击 A1，按 Ctrl + V 把文本粘贴到 A1，选中 B1，输入公式 =CLEAN(A1)，按回车，返回“Excel函数使用教程”，文字之间仍然还有一个空格，把它复制回文本窗口与原文本对比可知；操作过程步骤，如图2所示：

图2

2、公式说明：

A、公式 =CLEAN(A1) 不能把“Excel函数使用教程”中的非打印空格都删除，还留下一个，如果要把该空格也删除请用下面的方法。

三、Excel Clean函数删除不了的非打印字符的去除方法

（一）删除字符之间的所有空格

1、同样以删除“Excel函数使用教程”的空格为例。双击 A1 单元格，选中一个空格，按 Ctrl + C 复制；选中 B1，输入公式 =SUBSTITUTE(A1," 后，按 Ctrl + V 把一个空格粘贴为被替换字符，接着输入 ","")，按回车，返回字符间所有空格被替换掉的文本“Excel函数使用教程”；操作过程步骤，如图3所示：

图3

2、公式 =SUBSTITUTE(A1," ","") 说明：

Substitute函数是一个替换函数，它用于把一个字或一串字符替换另一个或一串字符；公式 =SUBSTITUTE(A1," ","") 中，A1 为要替换部分字符的文本，" " 为被替换字符，"" 替换字符，公式的意思是：用 "" 替换 A1 中的所有 " "。

（二）删除 Unicode 非打印字符

一些 Unicode 非打印字符，Clean函数不能删除，也需要用Substitute函数替换，方法如下：

1、假如要删除 Unicode 字符集中 ASCII 码为 127 的字符；双击 A1 单元格，选中文本后面的非打印字符，按 Ctrl + C 复制，选中 B1，输入公式 =SUBSTITUTE(A1,"，按 Ctrl + V 把刚才复制的字符粘贴一份，继续输入 ","")，按回车，则所有非打印字符被删除；双击 B2，把公式 =SUBSTITUTE(A1,CHAR(127),"") 复制到 B2，按回车，A1 中的所有非打印字符也被删除；双击 A3，里有用 CHAR(127）表示的非打印字符，把公式 =SUBSTITUTE(A3,CHAR(127),"") 复制到 B3，按回车，A3 中的所有非打印字符也被去掉；操作过程步骤，如图4所示：

图4

2、公式说明：

A、公式 =SUBSTITUTE(A1,"","") 与 =SUBSTITUTE(A1,CHAR(127),"") 都用空文本 "" 替换 ASCII 码为 127 的非打印字符；两个公式的不同之处在于，前者把 A1 中非打印字符复制到公式中，后者直接用 CHAR(127) 返回非打印字符。

B、A3 中的非打印字符是用 CHAR(127) 返回，在替换公式 =SUBSTITUTE(A3,CHAR(127),"") 中，直接用 CHAR(127) 作为被替换字符也可以把它们全部替换掉。

提示：如果用以上方法仍然不能把非打印字符删除，可先用Left函数、Mid函数或Right函数截取一个非打印字符作为被替换字符，然后就可以删除了，具体请参考《Excel Trim函数用法的6个实例，含前后空格与Trim不起作用且不能复制空格的去除及数值带空格的求和》一文。

四、Excel Clean函数的扩展应用实例

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

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

图5

2、公式 =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（需关闭中文输入法），打开“设置单元格格式”窗口，再选择“数字”选项卡，最后选择左边“数值”即可。