Clean函数用于删除Excel中文本中所有不可打印的字符。如果这些字符位于文本前后&＃xff0c;则可以全部删除&＃xff1b;如果它们位于字符或单词之间&＃xff0c;则不会全部删除&＃xff0c;而是保留一个字符。Clean函数的工作方式与Trim函数类似&＃xff0c;只是Trim函数用于删除空间。

Clean函数只能删除ASCII代码为0到31的非打印字符。无法删除某些非打印Unicode字符。这种情况需要替换为替换函数&＃xff1b;如果仍然不能删除&＃xff0c;则需要合并Left、Mid、Right、Code和其他函数。此外&＃xff0c;Clean的文本参数可以是数组&＃xff0c;此函数可以一次删除一列中的所有非打印字符。如果求和值包含非打印字符&＃xff0c;则需要删除这些字符&＃xff0c;然后使用Clean对其求和。

一、 Excel Clean函数的语法

1、表达式&＃xff1a;干净(文本)

2、说明&＃xff1a;

A、 Clean函数用于删除文本中的所有非打印字符。这些非打印字符是指在ASCII代码表中编码为0-31的字符。有些是空间&＃xff0c;有些不是。有关详细信息&＃xff0c;请参阅文章“ASCII表”。

B、 除ASCII代码表中的非打印字符外&＃xff0c;Unicode字符集也有非打印字符&＃xff0c;其值为127、129、141、143、144和157&＃xff0c;Clean函数不能删除它们&＃xff0c;要删除&＃xff0c;需要使用替换函数。

C、 参数文本是删除非打印字符的文本&＃xff1b;Clean函数可以删除文本前后的所有非打印字符&＃xff0c;也可以删除文本之间的非打印字符&＃xff0c;但它不会删除字符之间的所有非打印字符&＃xff0c;它会留下一个&＃xff0c;这与用于删除空间的Trim函数相同。

二、 Excel Clean函数实例

(一) 删除文本前后的所有非打印字符

1、如果要删除文本“Excel函数教程”前后的所有非打印字符。双击单元格A1&＃xff0c;将公式&＃61;CHAR(9)&“Excel函数教程”&CHAR(11)复制到A1&＃xff0c;选择B1&＃xff0c;输入公式&＃61;CLEAN(A1)&＃xff0c;按enter&＃xff0c;返回“Excel函数教程”&＃xff1b;选择A1:B1&＃xff0c;按Ctrl&＃43;C复制&＃xff0c;切换到空记事本窗口&＃xff0c;按Ctrl&＃43;V粘贴&＃xff0c;删除A1文本前后所有非打印字符&＃xff0c;选择“Excel函数教程”&＃xff0c;复制到A2&＃xff0c;在B2中输入公式&＃61;CLEAN(A2)&＃xff0c;按回车键&＃xff0c;返回“Excel函数教程”&＃xff0c;操作过程步骤如图1所示&＃xff1a;

图1

2、说明&＃xff1a;

A、 CHAR(9)返回“水平制表符”&＃xff0c;9是水平制表符的ASCII码&＃xff1b;CHAR(11)返回“垂直制表符”&＃xff0c;11是垂直制表符的ASCII码。

B、 Clean函数既可以删除Char函数返回的非打印字符&＃xff0c;也可以删除从上面演示的文本复制到单元格的非打印字符。

(二) 删除单词之间的非打印字符

1、如果要删除文本“Excel函数教程”中的非打印空间。在记事本窗口中选择要删除的文本&＃xff0c;按Ctrl&＃43;C复制&＃xff0c;切换到Excel窗口&＃xff0c;双击A1&＃xff0c;按Ctrl&＃43;V将文本粘贴到A1&＃xff0c;选择B1&＃xff0c;输入formula&＃61;CLEAN(A1)&＃xff0c;按enter&＃xff0c;返回“Excel函数教程”&＃xff0c;文本之间仍有空格&＃xff0c;将其复制回记事本窗口并比较原始文本&＃xff1b;过程步骤如图2所示&＃xff1a;

图2

2、公式说明&＃xff1a;

A、 公式&＃61;CLEAN(A1)不能删除“Excel函数教程”中的非打印空格&＃xff0c;只留下一个&＃xff0c;如果要删除空格&＃xff0c;请使用以下方法。

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

(一) 删除字符之间的所有空格

1、如果要删除“Excel func ti on tutorial”中的“func ti on”空格。双击单元格A1&＃xff0c;选择一个空格&＃xff0c;按Ctrl&＃43;C进行复制&＃xff1b;选择B1&＃xff0c;输入公式&＃61;SUBSTITUTE(A1&＃xff0c;”&＃xff0c;按Ctrl&＃43;V将空格粘贴为被替换的字符&＃xff0c;然后键入“&＃xff0c;”&＃xff0c;按回车键&＃xff0c;返回文本“Excel函数教程”&＃xff1b;操作步骤如图3所示&＃xff1a;

图3

2、公式&＃61;替换(A1&＃xff0c;“&＃xff0c;”)说明&＃xff1a;

Substitute函数是一个替换函数&＃xff0c;它将一个字符或一个字符串替换为另一个字符或另一个字符串&＃xff1b;A1是替换某些字符的文本&＃xff0c;“is replaced character”&＃xff0c;“replaces The character in The formula&＃61;Substitute(A1&＃xff0c;”&＃xff0c;“”)&＃xff0c;公式的意思是&＃xff1a;将A1中的“全部替换为”。

(二) 删除Unicode非打印字符

某些Unicode非打印字符不能被Clean函数删除&＃xff0c;还需要用替换函数替换它&＃xff0c;如下所示&＃xff1a;

1、如果要删除Unicode字符集中ASCII为127的字符&＃xff1b;双击单元格A1&＃xff0c;选择文本后面的非打印字符&＃xff0c;按Ctrl&＃43;C复制&＃xff0c;选择B1&＃xff0c;输入formula&＃61;SUBSTITUTE(A1&＃xff0c;”&＃xff0c;按Ctrl&＃43;V粘贴复制的字符&＃xff0c;继续键入“&＃xff0c;”)&＃xff0c;按enter&＃xff0c;删除所有非打印字符&＃xff1b;双击B2&＃xff0c;将公式&＃61;SUBSTITUTE(A1&＃xff0c;CHAR(127)&＃xff0c;“”复制到B2&＃xff0c;按Enter键&＃xff0c;同时删除A1中所有非打印字符&＃xff1b;双击A3&＃xff0c;将CHAR(127)表示的非打印字符&＃xff0c;将公式&＃61;SUBSTITUTE(A3&＃xff0c;CHAR(127)&＃xff0c;“”复制到B3&＃xff0c;按Enter键&＃xff0c;A3中所有非打印字符也被删除&＃xff0c;操作步骤如图4所示&＃xff1a;

图4

2、公式说明&＃xff1a;

A、 公式&＃61;SUBSTITUTE(A1&＃xff0c;“&＃xff0c;”)和&＃61;SUBSTITUTE(A1&＃xff0c;CHAR(127)&＃xff0c;“”)用空文本“”替换ASCII代码为127的非打印字符&＃xff1b;这两个公式的区别在于前者将A1中的非打印字符复制到公式中&＃xff0c;后者用CHAR(127)直接返回非打印字符。

B、 A3中的非打印字符用CHAR(127)返回&＃xff0c;您也可以用CHAR(127)替换它们&＃xff0c;作为替换公式&＃61;SUBSTITUTE(A3&＃xff0c;CHAR(127)&＃xff0c;“”中的替换字符。

提示&＃xff1a;如果使用上述方法不能删除非打印字符&＃xff0c;可以使用Left、Mid或Right函数截取非打印字符作为替换字符&＃xff0c;然后删除。有关详细信息&＃xff0c;请参阅文章“如何使用Excel Trim函数(6个示例&＃xff0c;带前导、尾随、顽固空格和左键)”。

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

(一) Sum&＃43;Clean函数组合添加带有非打印字符的数字

1、如果要对其值为文本且前面有非打印字符的列求和。双击单元格D9&＃xff0c;输入公式&＃61;SUM(D2:D8)&＃xff0c;按enter键&＃xff0c;返回0&＃xff1b;再次双击D9&＃xff0c;将公式改为&＃61;SUM(VALUE(CLEAN(D2:D8))&＃xff0c;按Ctrl&＃43;Shift&＃43;enter键&＃xff0c;返回求和结果5028&＃xff1b;操作过程步骤如图5所示&＃xff1a;

图52、公式&＃61;和(值(干净(D2:D8)))说明&＃xff1a;

A、 公式&＃61;SUM(D2:D8)返回0&＃xff0c;因为D2:D8中的值前面有非打印字符。计算时&＃xff0c;Sum函数无法将它们转换为数值。

B、 公式&＃61;SUM(VALUE(CLEAN(D2:D8))是一个数组公式&＃xff0c;数组公式需要按Ctrl&＃43;Shift&＃43;Enter返回计算结果。

C、 D2:D8将D2到D8中的所有值作为数组返回。然后&＃xff0c;Clean函数删除每个值前面的非打印字符&＃xff0c;最后返回数组{“638”&＃xff1b;“982”&＃xff1b;“897”&＃xff1b;“892”&＃xff1b;“528”&＃xff1b;“329”&＃xff1b;“762”}。

D、 公式变成&＃61;和(值({“638”&＃xff1b;“982”&＃xff1b;“897”&＃xff1b;“892”&＃xff1b;“528”&＃xff1b;“329”&＃xff1b;“762”})&＃xff0c;进一步计算&＃xff0c;数组中的所有元素都是从文本转换的数值。

E、 公式变为&＃61;SUM({638&＃xff1b;982&＃xff1b;897&＃xff1b;892&＃xff1b;528&＃xff1b;329&＃xff1b;762})&＃xff0c;最后用SUM函数对数组求和。

提示&＃xff1a;如果在D9中一次求和后仍不能求和&＃xff0c;则需要将D9的单元格格式设置为“数字”&＃xff0c;按Ctrl&＃43;1组合键&＃xff0c;打开“格式单元格”窗口&＃xff0c;选择“数字”页签&＃xff0c;最后选择左侧的“数字”。