文本处理：LEFT/RIGHT/MID、TEXT、& 与 SUBSTITUTE 清洗

系统导出的数据很少有干净的：姓名部门连在一起、手机号中间几位要打码、日期格式五花八门、编号前后带空格。文本函数就是数据清洗的主力工具。这一篇按「截取、定位、替换、拼装」四类动作来讲，每类都给出可复制的公式。

截取类：LEFT / RIGHT / MID

三个函数分别从左、从右、从中间截取字符（LEFT、RIGHT 各接一个长度，MID 接起始位置和长度）：

=LEFT("张三-销售部",2)      ' 得「张三」
=RIGHT("13812345678",4)    ' 得「5678」
=MID("20260915",5,2)       ' 得「09」

LEFT 和 RIGHT 各要一个长度参数，MID 多一个起始位置。参数顺序记法：MID（文本，从哪开始，取多长）——中文里念出来就是「从哪开始取多长」，自然得不用背。

配合 FIND 定位动态分隔符，拆分才够通用。比如「部门-姓名」格式要取姓名：

=RIGHT(A2,LEN(A2)-FIND("-",A2))

LEN 取总长度，FIND 找「-」的位置，两者相减就是「-」后面的字符数。分隔符不确定在第几位时，这个组合比硬写 MID 的位置稳。

定位类：FIND / SEARCH / LEN

  • FIND(查找文本, 源文本, [开始位置])：区分大小写。
  • SEARCH(查找文本, 源文本, [开始位置])：不区分大小写，支持通配符。

日常清洗用 SEARCH 更宽容。典型组合：判断手机号位数并打码——

=LEFT(A2,3)&"****"&RIGHT(A2,4)

先无条件信任长度是 11 位；不信任就先 LEN 判断（不等于 11 的单独标出人工核对）。想系统学习「按模式匹配文本」的方法论，可以看 正则表达式从入门到精通，Excel 的 FIND+MID 组合本质上就是正则的「手工版」，理解正则后很多清洗可以一条规则走天下。

替换类：SUBSTITUTE / TRIM / CLEAN

  • SUBSTITUTE(text, old_text, new_text, [instance_num])：替换。把带括号后缀的部门名统一：
=SUBSTITUTE(A2,"（华东）","")

全角括号也要一起替换时，写两个 SUBSTITUTE 嵌套。还可以指定只替换第几次出现（第四参数）。 - TRIM(text)：去掉首尾空格，并把中间多个连续空格压成一个。系统导出的「张三 」靠它救。 - CLEAN(text)：删除不可打印的控制字符（从网页复制的数据常带）。

函数 清理对象 典型来源
TRIM 首尾与多余空格 系统导出、手工录入
CLEAN 不可打印控制字符 网页复制、 XML 导出
SUBSTITUTE 指定字符串 口径不统一的部门名、旧编码

实战中最常见的清洗流水线是「先 TRIM 再替换最后比较」：

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

CHAR(160) 是不间断空格，肉眼看不见但 VLOOKUP 匹配不上它，从网页复制的数据十有八九带。

格式转换：TEXT 与 TEXTSPLIT 边界

TEXT(value, format_text) 把数值按格式代码转成文本。常用格式代码与效果对照：

格式代码 结果示例 用途
$#,##0.00 $1,234.57 两位小数金额
YYYY/MM/DD 2026/10/22 标准日期
yyyy年m月d日 2026年10月22日 中文日期
aaaa 星期四 中文星期
0.0% 28.5% 一位小数百分比

套用方式统一为把单元格套进 TEXT。以当天日期为例，结果就是 2026/10/22 这样的标准格式。

关键认知：TEXT 的结果是文本，不能再当数字计算。官方建议保留原始值，另起单元格做 TEXT，后续公式引用原始值。中文格式代码（年月日、aaaa 星期）依赖系统区域和语言包，显示异常时先确认 Excel 语言设置，以你的版本为准。

文本拆列还有一条捷径：Excel 2013 起的「快速填充」（Ctrl+E），按示例自动识别规律，快捷键一篇有详细用法。它方便但不可控、不可复制，正式模板还是函数稳。

一步到位的 TEXTSPLIT：Microsoft 365 和 Excel 2022 起提供 TEXTSPLIT 函数，一条公式按分隔符把文本拆成多列（甚至拆行）：

=TEXTSPLIT(A2,"-")

「张三-销售部」直接拆成两列，连拖公式都省了。低版本没有这个函数，老老实实用 LEFT/FIND 组合或「数据→分列」。分列是顿号、逗号、空格等固定分隔符的首选，函数适合分隔符位置不固定的复杂场景。

拼装类：& 与 CONCAT/TEXTJOIN

最经典的是 & 运算符，连接文本与单元格：

="尊敬的"&A2&"："&CHAR(10)&"您本月迟到"&B2&"次，请注意考勤。"

CHAR(10) 是换行符（需开启自动换行才显示），拼通知类文本时几乎必用。Excel 2016 起新增 CONCAT（替代 CONCATENATE，支持区域）和 Excel 2019/365 的 TEXTJOIN（用分隔符批量连接，可跳过空值）：

=TEXTJOIN("、",TRUE,B2:D2)

把三项并列内容用顿号连接，TRUE 表示忽略空单元格。发通知、做名单拼接时极好用，几百人的名单一条公式就拼完。版本提醒：TEXTJOIN 需 Excel 2019 或 Microsoft 365，2016 没有，低版本只能靠 & 逐个连——做模板发给旧版同事前先确认这点。

实战：从打卡记录提取时间

假设 A 列是「2026-09-01 08:52:31」格式的文本，要拆出日期和小时：

日期：=DATEVALUE(LEFT(A2,10))
小时：=VALUE(MID(A2,12,2))

DATEVALUE 把日期文本转成真正的日期序列值，VALUE 把数字文本转成数值。拆完这两列才能被透视表和条件统计正常处理——这就是第一章说的「容器」的颗粒度。

再进一步，如果导出的日期是「2026/9/1 上午 08:52」这种带中文的混合格式，套路一样：先找分隔符位置，再截取转换。复杂格式批量处理推荐用「数据→分列」配合函数，分列负责粗拆，函数负责精修。

清洗流水线的标准节奏

不管导出格式多花哨，清洗顺序固定四步，别跳着来：

步骤 动作 用的函数
一 备份原列，复制出清洗列 无
二 清不可见字符 CLEAN、CHAR(160) 替换
三 修空格和全角符号 TRIM、SUBSTITUTE
四 类型转换 VALUE、DATEVALUE

先清再看，而不是边看边改——顺序乱了，后面的步骤会覆盖前面的成果，半天找不到哪里被动过。每一步只做一件事，做完检查一遍再进下一步。

新手常见误区

  1. 在源数据上直接替换。清洗至少留一份原始导出，改坏了能回滚。养成「先复制一列，再动刀」的习惯。
  2. FIND 找不到时报错没兜底。先判有没有分隔符，比一片 #VALUE! 专业：
=IFERROR(FIND("-",A2),0)
  1. 以为 SUBSTITUTE 区分大小写是谣言。它确实区分，替换英文关键字时注意大小写变体。
  2. TEXT 的结果又喂回 SUM。文本进 SUM 得 0，还找不到原因，记住「TEXT 只用于展示」。
  3. 全角半角符号不分。全角括号「（）」和半角「()」是两个字符，SUBSTITUTE 只换你写的那一个。清洗前先把符号统一，否则同一条规则要写两遍。

清洗是脏活，但它是所有分析的地基。把常用清洗公式按场景存成自己的「公式库」，配合笔记工具归档，你的效率会随复用次数指数上升——第一次写半小时，第二次复制改区域，第三次起就是十秒钟的事。

本章由 WorkBuddy 原创整理，函数行为以 Microsoft Excel 官方文档为准。