OmniDocExcel数据清洗常用函数有哪些，怎么用
整理 Excel 里常用的文本清洗函数，说明每个函数处理哪类问题，给出用辅助列清洗数据的步骤和核对方法，帮你选对函数、保住原始数据。
从系统导出的客户名单、从网页复制的表格、从 PDF 转出的数据，常见问题都差不多：首尾多了空格，夹着看不见的字符，数字被存成了文字，同一列的日期格式混乱。几百行手工修改既慢又容易漏。Excel 里有一组专门处理文本的函数，各管一类问题，选对函数，几步就能把一列数据整理干净。
这篇文章写给需要整理表格的办公人员、学生和数据分析新手。重点回答两个问题：每个常用函数负责哪类清洗工作，遇到具体问题时该用哪个；以及怎样在不损坏原数据的前提下完成清洗并做检查。
先给结论。
清洗单元格文字，用哪个函数？一句话结论：先判断问题属于哪一类，再选对应函数，用辅助列写公式，确认无误后再替换原数据。
| 你的情况 | 推荐做法 | 原因 |
|---|---|---|
| 文字前后或中间有多余空格 | TRIM | 去掉首尾空格，并把连续空格压缩成一个 |
| 从网页或系统复制后有看不见的字符 | CLEAN 配合 TRIM | CLEAN 去除不可打印字符，TRIM 处理空格 |
| 空格看起来正常却清不掉 | SUBSTITUTE 替换 CHAR(160) | 这通常是不间断空格，TRIM 不处理它 |
| 数字左对齐、求和结果为 0 | VALUE 或分列功能 | 数字以文本形式存储，需要转换为数值 |
| 一个单元格里包含多段信息 | MID、LEFT、RIGHT 配合 FIND | 按位置或分隔符把信息拆出来 |
开始之前：先确认这几点
动手前的判断决定了后面省多少事。
- 先看清问题是什么。选中几行肉眼检查，再用 LEN 统计字符数。字符数比预期多，通常说明有隐藏的空格或不可见字符。
- 保留原始数据。在数据右侧留出空列写公式，不直接覆盖源列。万一公式写错，原数据还在，可以回退。
- 区分文本数字和数值。数字默认右对齐，若显示在左侧，多半是文本。文本数字无法参与求和，也可能导致排序结果异常。
- 确认数据来源。从网页或其他系统复制的内容，容易带入不可见字符。从 PDF 转出的表格，常见合并单元格和换行问题。如果数据在 PDF 里，可以先用 OmniDoc 桌面客户端的 PDF 编辑器导出为 Excel 格式，再进行清洗；扫描件则需要先做 OCR 识别，识别结果要人工核对。
- 写下清洗标准。例如日期统一为“年-月-日”，手机号去掉空格和横线，地址中的省市用统一简称。标准写清楚，公式才好定。
用辅助列清洗一列文本的步骤
下面以 A 列的客户姓名为例，演示完整流程。
- 在 B 列（或数据右侧任意空列）第一行写公式
=TRIM(A2)，引用原单元格。 - 如果需要同时去除不可见字符，把公式改为
=TRIM(CLEAN(A2))，先去掉不可打印字符，再处理空格。 - 将公式向下填充到最后一行，逐行检查结果是否符合预期。
- 在另一列用
=LEN(A2)-LEN(B2)统计每行被删掉的字符数，确认差值与你的判断一致。 - 选中 B 列，复制后用“选择性粘贴”的数值选项粘贴回 A 列，或保存到新列，把公式结果固定下来。
- 删除辅助列，另存为新文件，避免与原始文件混淆。
如果要从“北京-朝阳-0012”这类文本中取出中间的区名，可以用 =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)。思路是先用 FIND 找到两个分隔符的位置，再让 MID 截取它们之间的内容。
做完后逐项核对
清洗完成不等于结果正确，建议逐项检查。
| 检查项 | 怎么看 | 有问题怎么办 |
|---|---|---|
| 首尾空格是否清除 | 对结果列用 LEN，看长度是否与肉眼预期一致 | 检查公式引用的行号是否错位 |
| 数字能否参与计算 | 对该列使用 SUM，结果为 0 说明仍是文本 | 用 VALUE 转换，或用分列功能批量转换 |
| 是否残留不可见字符 | 对比 LEN 结果，找出异常偏大的行 | 用 SUBSTITUTE 把 CHAR(160) 替换为普通空格 |
| 日期格式是否统一 | 看对齐方式，并抽查几行的显示效果 | 用 DATEVALUE 转换，或统一设置单元格格式 |
| 是否出现错误值 | 筛选或查找 #VALUE! 之类的错误 | 用 IFERROR 包裹，或回到源数据修正 |
机器识别或自动转换的结果，如 OCR 识别出的表格，尤其要逐条核对数字和人名，机器可能把相似字符误认。
📊 核心对比
| 函数 | 主要用途 | 示例 | 注意事项 |
|---|---|---|---|
| TRIM | 去除首尾空格，压缩中间多余空格 | =TRIM(A2) | 不处理不间断空格 |
| CLEAN | 去除不可打印字符 | =CLEAN(A2) | 不处理普通空格 |
| SUBSTITUTE | 按内容替换 | =SUBSTITUTE(A2,"-","") | 会替换所有匹配项 |
| REPLACE | 按位置替换 | =REPLACE(A2,1,3,"") | 位置数错会改错内容 |
| LEFT、MID、RIGHT | 按位置截取 | =LEFT(A2,4) | 常与 FIND 配合使用 |
| FIND | 定位字符位置 | =FIND("-",A2) | 找不到时返回错误值 |
| VALUE | 文本转数字 | =VALUE(A2) | 含非数字字符时报错 |
| PROPER、UPPER、LOWER | 统一英文大小写 | =PROPER(A2) | 只影响英文字母 |
| CONCAT、TEXTJOIN | 合并多列或多格内容 | =TEXTJOIN(" ",TRUE,A2:C2) | TEXTJOIN 在部分旧版本中不可用 |
| IFERROR | 出错时显示替代内容 | =IFERROR(VALUE(A2),"") | 可能掩盖真实问题，慎用 |
❓ 常见问题（FAQ）
1. TRIM 处理过了，为什么空格还清不掉？
很可能是不间断空格（字符编码 160）。TRIM 只识别普通空格，需要先用 =SUBSTITUTE(A2,CHAR(160)," ") 把它替换掉，再对结果使用 TRIM。
2. 公式结果能直接当作普通数据用吗？
公式结果依赖原列，原列变动后会跟着变。确认无误后，应使用“选择性粘贴”中的数值选项，把公式结果固定下来，再删除源列或辅助列。
3. 数据量很大、每周都要重复同样的清洗，有没有更省事的办法？
可以考虑 Excel 自带的数据查询和转换工具，把清洗步骤记录下来，下次导入新数据时一键重跑。步骤固定的任务适合这种方式，一次性的小表格用辅助列就够了。
4. 表格在 PDF 里，能直接清洗吗？
PDF 通常不能直接当作表格处理。文字版 PDF 可以先导出为 Excel 格式再清洗；扫描版则需要先 OCR 识别。无论哪种方式，导出或识别后都要对照原文件核对数字和文字。
📝 总结
Excel 数据清洗的关键，是先判断问题属于空格、不可见字符、文本数字还是需要拆分的内容，再选对应的函数。用辅助列写公式、用 LEN 核对结果、确认后再固定为数值，这套流程能让清洗过程既快又可控。
