ARTICLE · 1215050
Excel技巧|文本清理三兄弟:TRIM、CLEAN、SUBSTITUTE,脏数据一键洗干净
Excel技巧|文本清理三兄弟:TRIM、CLEAN、SUBSTITUTE,脏数据一键洗干净前几期讲了文本提取三兄弟LEFT、RIGHT、MID，有同学问：“提取之前数据里全是空格和奇怪符号怎么办？” 这就是今天要解决的问题：文本清理。 从系统导出的数据，经常带着一堆看不见的“垃圾”——前后多余的空格、换行符、制表符、特殊符号。这些字符肉眼看不见，但会让VLOOKUP查不到、COUNTIF数不对、数据匹配全失败。 今天讲文本清理三兄弟：TRIM、CLEAN、SUBSTITUTE，专治各种脏数据。 TRIM的作用是：去掉文本首尾的所有空格，同时把中间连续的多个空格压缩成一个。 从系统导出的姓名，有的前面带空格，有的后面带空格，看起来都是“张三”，但VLOOKUP就是查不到。 =TRIM(A2) “ 张三 ”变成“张三”，前后空格全清掉。 场景二：清理中间的多余空格 “张 三”（中间两个空格）用TRIM后变成“张 三”（中间一个空格）。 注意：TRIM只能处理英文空格（按空格键输入的那种）。如果数据里是中文全角空格或不换行空格，TRIM处理不了，需要用SUBSTITUTE。 语法：=CLEAN(文本) CLEAN的作用是：删除文本中所有不可打印的字符，比如换行符、制表符、回车符等。 场景一：去掉从网页复制的换行符 从网页复制数据到Excel，单元格里经常藏着换行符（看起来是一行，实际上里面有个隐藏的换行）。这种数据没法VLOOKUP，也没法筛选。 =CLEAN(A2) 换行符被删掉，文本变成干净的一行。 场景二：去掉制表符 从PDF或系统导出的数据，经常带着制表符（Tab），打印时看不出来，但会影响数据匹配。 =CLEAN(A2) 所有不可打印字符被清理干净。 注意：CLEAN只删不可打印字符，不删空格。空格要用TRIM处理。 语法：=SUBSTITUTE(文本, 要替换的旧字符, 替换成的新字符, [替换第几个]) SUBSTITUTE的作用是：把文本里的某个字符，替换成另一个字符。 场景一：去掉中文全角空格 “张 三”（中间是全角空格），TRIM处理不了，用SUBSTITUTE： =SUBSTITUTE(A2, " ", "") 把全角空格替换成空，相当于删掉。 场景二：去掉不换行空格 从网页复制来的数据里，经常有不换行空格（CHAR(160)），TRIM也处理不了： =SUBSTITUTE(A2, CHAR(160), "") 场景三：统一分隔符 数据里有的用逗号，有的用分号，想统一成逗号： =SUBSTITUTE(A2, ";", ",") 把分号全部替换成逗号。 场景四：去掉特定符号 手机号里带了横杠“138-0013-8000”，想去掉横杠： =SUBSTITUTE(A2, "-", "") 结果是“13800138000”。 第四个参数：如果要替换的不是全部，而是第几个，可以填第四个参数。比如=SUBSTITUTE(A2, "-", "", 1)只替换第一个横杠。不填就全部替换。 单个函数解决不了的问题，组合起来就行。 场景一：彻底清洗数据（空格+不可打印字符+全角空格） =TRIM(CLEAN(SUBSTITUTE(A2, " ", ""))) 从里到外：先把全角空格替换掉，再清理不可打印字符，最后去掉多余空格。三步走完，数据干干净净。 场景二：去掉所有空格（包括中间的） =SUBSTITUTE(A2, " ", "") TRIM只去首尾空格，中间多个空格压缩成一个。如果想全部删掉，用SUBSTITUTE把空格替换成空。 场景三：去掉换行符 =SUBSTITUTE(A2, CHAR(10), "") CHAR(10)是换行符，替换成空就是删掉。CLEAN也能删，但SUBSTITUTE更精准。 翻车一：TRIM处理不了全角空格 中文全角空格TRIM不认，需要用SUBSTITUTE替换。 翻车二：CLEAN删不掉空格 CLEAN只管不可打印字符，空格不在它的处理范围内。空格用TRIM。 翻车三：SUBSTITUTE区分大小写 SUBSTITUTE是区分大小写的。如果要忽略大小写替换，用REPLACE函数或者先用UPPER/LOWER统一。 翻车四：清理完还是查不到 如果TRIM+CLEAN+SUBSTITUTE都处理了，VLOOKUP还是查不到，检查两边的数据类型是否一致（文本型数字vs数值型数字），以及是否有合并单元格导致的数据错位。 下次从系统导出数据，先跑一遍清理三兄弟，再去做查找和统计。数据干净了，后面的事才顺。
一、TRIM：去掉多余空格（最常用）
语法：=TRIM(文本)
