Clean函数用于删除Excel中文本中所有不可打印的字符。如果这些字符位于文本前后,则可以全部删除;如果它们位于字符或单词之间,则不会全部删除,而是保留一个字符。Clean函数的工作方式与Trim函数类似,只是Trim函数用于删除空间。
Clean函数只能删除ASCII代码为0到31的非打印字符。无法删除某些非打印Unicode字符。这种情况需要替换为替换函数;如果仍然不能删除,则需要合并Left、Mid、Right、Code和其他函数。此外,Clean的文本参数可以是数组,此函数可以一次删除一列中的所有非打印字符。如果求和值包含非打印字符,则需要删除这些字符,然后使用Clean对其求和。
一、 Excel Clean函数的语法
1、表达式:干净(文本)
2、说明:
A、 Clean函数用于删除文本中的所有非打印字符。这些非打印字符是指在ASCII代码表中编码为0-31的字符。有些是空间,有些不是。有关详细信息,请参阅文章“ASCII表”。
B、 除ASCII代码表中的非打印字符外,Unicode字符集也有非打印字符,其值为127、129、141、143、144和157,Clean函数不能删除它们,要删除,需要使用替换函数。
C、 参数文本是删除非打印字符的文本;Clean函数可以删除文本前后的所有非打印字符,也可以删除文本之间的非打印字符,但它不会删除字符之间的所有非打印字符,它会留下一个,这与用于删除空间的Trim函数相同。
二、 Excel Clean函数实例
(一) 删除文本前后的所有非打印字符
1、如果要删除文本“Excel函数教程”前后的所有非打印字符。双击单元格A1,将公式=CHAR(9)&“Excel函数教程”&CHAR(11)复制到A1,选择B1,输入公式=CLEAN(A1),按enter,返回“Excel函数教程”;选择A1:B1,按Ctrl+C复制,切换到空记事本窗口,按Ctrl+V粘贴,删除A1文本前后所有非打印字符,选择“Excel函数教程”,复制到A2,在B2中输入公式=CLEAN(A2),按回车键,返回“Excel函数教程”,操作过程步骤如图1所示:
![3f2efd81ae607a43547eedc87593bb86.gif](https://i-blog.csdnimg.cn/blog_migrate/24c6ac04960b57b6221298ac94cca3a1.gif)
图1
2、说明:
A、 CHAR(9)返回“水平制表符”,9是水平制表符的ASCII码;CHAR(11)返回“垂直制表符”,11是垂直制表符的ASCII码。
B、 Clean函数既可以删除Char函数返回的非打印字符,也可以删除从上面演示的文本复制到单元格的非打印字符。
(二) 删除单词之间的非打印字符
1、如果要删除文本“Excel函数教程”中的非打印空间。在记事本窗口中选择要删除的文本,按Ctrl+C复制,切换到Excel窗口,双击A1,按Ctrl+V将文本粘贴到A1,选择B1,输入formula=CLEAN(A1),按enter,返回“Excel函数教程”,文本之间仍有空格,将其复制回记事本窗口并比较原始文本;过程步骤如图2所示:
![2ac08babd4cb2d71c02cb1b02ab46612.gif](https://i-blog.csdnimg.cn/blog_migrate/029201009b33441f4c0d3fcb34e7d1c4.gif)
图2
2、公式说明:
A、 公式=CLEAN(A1)不能删除“Excel函数教程”中的非打印空格,只留下一个,如果要删除空格,请使用以下方法。
三、 Excel Clean函数不能删除非打印字符的删除方法
(一) 删除字符之间的所有空格
1、如果要删除“Excel func ti on tutorial”中的“func ti on”空格。双击单元格A1,选择一个空格,按Ctrl+C进行复制;选择B1,输入公式=SUBSTITUTE(A1,”,按Ctrl+V将空格粘贴为被替换的字符,然后键入“,”,按回车键,返回文本“Excel函数教程”;操作步骤如图3所示:
![3490156311d39991471d4e21b2fa1baf.gif](https://i-blog.csdnimg.cn/blog_migrate/c4e5878b2cfaee23332fd2a87c781165.gif)
图3
2、公式=替换(A1,“,”)说明:
Substitute函数是一个替换函数,它将一个字符或一个字符串替换为另一个字符或另一个字符串;A1是替换某些字符的文本,“is replaced character”,“replaces The character in The formula=Substitute(A1,”,“”),公式的意思是:将A1中的“全部替换为”。
(二) 删除Unicode非打印字符
某些Unicode非打印字符不能被Clean函数删除,还需要用替换函数替换它,如下所示:
1、如果要删除Unicode字符集中ASCII为127的字符;双击单元格A1,选择文本后面的非打印字符,按Ctrl+C复制,选择B1,输入formula=SUBSTITUTE(A1,”,按Ctrl+V粘贴复制的字符,继续键入“,”),按enter,删除所有非打印字符;双击B2,将公式=SUBSTITUTE(A1,CHAR(127),“”复制到B2,按Enter键,同时删除A1中所有非打印字符;双击A3,将CHAR(127)表示的非打印字符,将公式=SUBSTITUTE(A3,CHAR(127),“”复制到B3,按Enter键,A3中所有非打印字符也被删除,操作步骤如图4所示:
![1d551a9c3c7a2b527cbca40fcd90d238.gif](https://i-blog.csdnimg.cn/blog_migrate/4e6559502b50a134b27f51809b22ae7d.gif)
图4
2、公式说明:
A、 公式=SUBSTITUTE(A1,“,”)和=SUBSTITUTE(A1,CHAR(127),“”)用空文本“”替换ASCII代码为127的非打印字符;这两个公式的区别在于前者将A1中的非打印字符复制到公式中,后者用CHAR(127)直接返回非打印字符。
B、 A3中的非打印字符用CHAR(127)返回,您也可以用CHAR(127)替换它们,作为替换公式=SUBSTITUTE(A3,CHAR(127),“”中的替换字符。
提示:如果使用上述方法不能删除非打印字符,可以使用Left、Mid或Right函数截取非打印字符作为替换字符,然后删除。有关详细信息,请参阅文章“如何使用Excel Trim函数(6个示例,带前导、尾随、顽固空格和左键)”。
四、 Excel Clean函数的扩展应用实例
(一) Sum+Clean函数组合添加带有非打印字符的数字
1、如果要对其值为文本且前面有非打印字符的列求和。双击单元格D9,输入公式=SUM(D2:D8),按enter键,返回0;再次双击D9,将公式改为=SUM(VALUE(CLEAN(D2:D8)),按Ctrl+Shift+enter键,返回求和结果5028;操作过程步骤如图5所示:
![dc0a7bf1f1e02368cd8d216b564c9cb7.gif](https://i-blog.csdnimg.cn/blog_migrate/6e870fe08ab926f94ae83475564be81a.gif)
图52、公式=和(值(干净(D2:D8)))说明:
A、 公式=SUM(D2:D8)返回0,因为D2:D8中的值前面有非打印字符。计算时,Sum函数无法将它们转换为数值。
B、 公式=SUM(VALUE(CLEAN(D2:D8))是一个数组公式,数组公式需要按Ctrl+Shift+Enter返回计算结果。
C、 D2:D8将D2到D8中的所有值作为数组返回。然后,Clean函数删除每个值前面的非打印字符,最后返回数组{“638”;“982”;“897”;“892”;“528”;“329”;“762”}。
D、 公式变成=和(值({“638”;“982”;“897”;“892”;“528”;“329”;“762”}),进一步计算,数组中的所有元素都是从文本转换的数值。
E、 公式变为=SUM({638;982;897;892;528;329;762}),最后用SUM函数对数组求和。
提示:如果在D9中一次求和后仍不能求和,则需要将D9的单元格格式设置为“数字”,按Ctrl+1组合键,打开“格式单元格”窗口,选择“数字”页签,最后选择左侧的“数字”。