文本函数：拆分合并和清洗

清洗数据：文本函数的主战场

收上来的表格十有八九要清洗。姓名和工号挤在一个格子里——张三括号 10023；金额前面带着货币符号；从系统导出的日期长这样一长串带零点的时间；一串名字之间用顿号或者空格混连。逐个手动改能改到天亮，文本函数就是为此存在的。可以说不会文本函数的人，做表只能算完成了一半。

文本函数的输出几乎都是文本类型，这一点先记住。从左取、从右取、从中间取，出来的结果都是文本，要当数字用得用 VALUE 转换或者在前面加双减号。反过来数字要当文本用，TEXT 函数登场。类型之间的转换是清洗题里永恒的主题，基础概念在单元格和数据类型。

从一串混合内容里截取一段，LEFT、RIGHT、MID 三件套包揽。从左截 N 个字符、从右截 N 个字符、从中间第 N 位开始截 M 个字符。A1 里是 张三括号 10023 这种写法，要工号就从第 4 个字符开始取 5 位，得到 10023。要括号外的姓名，得先算出左括号在第几位，减去 1 就是姓名长度，再用从左取。

配合截取的核心函数是 FIND，找人位置：数出某个字符首次出现在第几位。日常清洗用 FIND 居多，它区分大小写但日常够了。还有 LEN 数出一共几个字符。清洗界的第一个常用组合拳是 TRIM，把首尾和中间多余的空格清成单个。

替换文本有两个函数，分工不同。SUBSTITUTE 按内容替换：告诉它把什么换成什么，区域里所有匹配的都换，适合统一符号、去后缀这类批量活。REPLACE 按位置替换：告诉它从第几位开始、换掉几位、换成什么，适合固定位置的内容改写，比如把所有编号的前两位换成部门代码。日常清洗 SUBSTITUTE 出场率高得多，记住它就够了，REPLACE 等遇到位置固定的需求再学。

文本函数最强大的思想是嵌套：一个函数的输出喂给另一个函数。比如提取身份证里的出生日期：先从中间取 8 位，再把这段数字交给日期函数包装成真日期，一个公式完成提取和类型转换。再比如清洗公司名：先用替换去掉有限公司 后缀，再用 TRIM 收尾，一串操作写成嵌套公式。嵌套写多了，读起来像句子，这是文本函数的乐趣所在。

学文本函数的顺序建议这样排：先把 LEFT、RIGHT、MID、LEN、FIND 五个基础函数用熟，它们能解决八成的截取需求；然后是 TRIM 和两个替换函数，专治脏数据；最后是 TEXT 和 TEXTJOIN，负责输出漂亮的成品。每个函数配一个自己工作中的真实例子，边做边学比按函数清单顺序背一遍快得多。

分列和拆分：批量切开一列

文本函数拆列适合规律不复杂的情形，规则稍复杂就要分列上场。分列是数据选项卡里的一个向导，把一个格子里的内容按分隔符或固定宽度切成多列。姓名和工号这种带括号的，用分隔符方式，勾选其它并在框里填左括号，向导自动按括号切两段。从各个业务系统导出来的数据尤其依赖它，CSV 变 Excel 的第一道工序通常就是分列。

分列向导还有一个隐藏用法：类型转换。到了第三步，每列都能指定是常规、文本还是日期。从 OA 导出的文本日期格式，选中这列走分列，第三步把列格式改成日期，走完一遍日期就活了。这个招数是处理文本日期的常规手段，也是常用快捷键里数据清洗工具链的一环。

分隔符不止一种的时候，分列向导里可以同时勾选制表符、空格、逗号、分号等。但同时处理顿号混空格混逗号的多重分隔，会切出很多空列。更稳妥的是在分列前用替换先统一符号：把顿号和空格全换成逗号再分。预处理这一步省事，后面每一列都干净。

固定宽度方式适合固定位数的内容：18 位身份证里前 6 位是地区码、第 7 到 14 位是生日、后 4 位是序号。在向导第二步用标尺点几下，身份证一列切成三段，生日那段在第三步设成日期格式，还能顺带把文本变日期。这个流程是 HR 做员工表的保留节目。

分列值得注意的两个细节：一是它会覆盖右侧列的内容，操作前先插空列留缓冲；二是分列一次只能处理一列，多列要重复走。批量处理多列时，配合录制宏或者逐列点，总比手动擦格子快。

分列还有一个很容易忽略的用途：给文本数字转类型。选一列文本格式的数字走分列，第三步选常规，走完之后这列就是真数字了，比选择性粘贴乘 1 还干净。同一个入口，既拆文本又转类型，是数据选项卡里性价比最高的一个按钮。

合并文本：连接符和文本函数

拆是拆，合是合。发工资条想把姓名、月份、金额拼成一句话：张三 10 月工资 5800 元。用连接符把每个部分串起来，要原样显示的部分用引号裹起来，变量用单元格地址，交替拼接。写法和说话一样自然，比古老的合并单元格函数顺手得多。

新一点的版本提供 TEXTJOIN，专门为批量合并设计：能一次性合并一个区域，自动加分隔符，还能跳过空格子。把一列名字用顿号连成一串，空名字不会产生两个顿号。做名单汇总、地址拼接的场景，TEXTJOIN 值得优先学。老版本没有它，就用连接符一个一个拼，也能干就是费键盘。

数字变文本的格式化用 TEXT：把 3800 变成带千分位的文本、日期变成指定格式的字符串。拼句子时数字带上格式的关键就是这个函数，比如 姓名 工资是 带格式的金额 元，读起来像人话。注意 TEXT 出来的是文本，不能参与后续求和，该算的账要在转文本之前算完。

拼接还有两个小坑值得提前知道。第一，空格子参与拼接会变成 0：一格空着，拼出来句子中间冒出个 0，看好半天才发现。习惯做法是用 IF 判断一下空格子，或者干脆保证源数据没有空。第二，日期和 TRUE/FALSE 直接拼接会露出底层序列值和英文单词，所以遇到非文本类型，先 TEXT 包装再说。这两条坑几乎每个新手都踩过，提前知道能少改半小时。

大小写空格和隐形垃圾

清洗到后期全是细节。从网页和系统里复制来的数据，最脏的不是可见字符，而是不可见的：全角空格、不间断空格、回车换行、制表符。TRIM 只清普通空格和部分特殊空格，清不掉的要用替换函数手工处理。

回车符在格子里看不见，但会把文本函数算的位置整体偏移。LEN 算出来总是比你数的字数多。替换它要知道一个写法：换行符的字符代码是 10，用 CHAR 包装后替换。同理不间断空格的代码是 160。把这两行存在备忘录里，遇到数据死活对不上的时候掏出来试试，往往一招见效。

系统导出的数据还经常夹带前导零：工号 007 传到 Excel 变成 7。保住前导零的办法是把这一列设成文本类型再粘贴，或者用 TEXT 补零：三位数编号不够三位就前面补零，写出来是固定的格式代码。编号本身的位数信息在 HR 表、资产表里都是关键字段，丢了前导零的编号对不上，排查起来非常痛苦。

英文字母大小写统一也是常见需求。全大写、全小写、首字母大写三个函数对应三种格式。从各分公司收集上来的产品型号有的全大写有的全小写，归并前统一过一遍，后面查找时才不会因大小写差异报错。做查找匹配时大小写不一致是最隐蔽的翻车原因之一，统一步骤提前做，比事后排查省事得多。

清洗的一般流程总结一下：先 TRIM 去空格，再替换统一符号和去不可见字符，然后分列或文本函数拆字段，必要时统一大小写格式，最后把数字转回数值类型。按这个顺序走，再乱的表也能干净利落。流程固定之后，每种脏数据都有对应的招，你甚至会期待收到更乱的表来练手。

实战：把一列混格式地址拆成省市区

把前面学的串起来做一道综合题。系统导出的地址列长得五花八门：广东省深圳市南山区科技园、广东省 深圳市 南山区、广东深圳南山。目标是拆成三列：省、市、区。

第一步，统一分隔符。观察发现空格、横杠混用，先 SUBSTITUTE 把横杠换成空格，再一次 SUBSTITUTE 把两个空格换成一个（嵌套一层即可），再用 TRIM 收尾。第二步，按固定关键词切。省市区的名字长度不固定，分列帮不上忙，用 FIND 定位 省 市 区 三个字的位置，配合 MID 截取：=MID(A2,1,FIND("省",A2)-1) 得到省。第三步，兜底缺失关键词的行：有的地址只写 广东深圳南山，FIND 找不到 省 直接报错，包一层 IFERROR 跳过走下一套规则。

拆完别急着交活，用 LEN(A2) 和拆出的三段长度之和对比一下，相等说明没丢字，不等就是哪里切歪了。这道题没有高深函数，全是基础款的组合，关键是拆解思路：先统一格式，再找规律，最后兜底校验。真实工作中的清洗任务八成都是这个套路，剩下的两成靠你临时翻函数文档。

相关篇章

清洗完的数据往往要做判断和统计，IF 和多条件判断是下一站。清洗中涉及的文本日期和数字格式转换，原理都在日期和时间里。

常见问题

TRIM 为什么清不掉某些空格？

TRIM 只认识标准空格字符，对全角空格、不间断空格和其它特殊空白无效。用替换函数定向处理：不间断空格用字符代码 60 乘 3 的写法（实际是 160）替换，全角空格直接在替换框里粘贴一个全角空格进去换掉。判断格子里有没有隐形字符，用 LEN 比你肉眼的字数多半到几个就是有。

分列之后原来的数据还在吗？

分列会把选中列切开，内容依次填到右侧列。如果右侧有数据会被覆盖，分列前先插几列空列做缓冲，或者先复制到新表上操作。分列本身可以撤销，但养成留缓冲的习惯更稳妥，尤其是大批量处理时，撤销也救不回被覆盖的隔壁数据。

数字和日期拼接后变成了乱码怎么办？

那不是乱码，是日期的序列值或数字的直接拼接。直接用连接符连接日期格或数字格，Excel 拿底层序列号拼接。先格式化：把日期或数字交给 TEXT 转成看得懂的文本再拼接，读起来就正常了。同样的问题出现在 TRUE/FALSE 上，拼接时显示的是英文单词而不是 1 和 0。

替换函数怎么只换了一个？

替换函数有第四个参数指定替换第几次出现，不写就替换全部。如果公式只替换了部分，检查第四个参数是不是不小心写了数字。另外替换函数是区分大小写的，英文字符替换要留意，不区分大小写的场合换一个思路。

文本函数做出来的数字没法求和？

因为文本函数返回的是文本。两种转回方法：在公式外面套 VALUE；或者更简洁地，在公式前加上双减号。也可以整列复制后选择性粘贴为数值，或直接在分列第三步把列设为常规。养成 收尾转数字 的意识，清洗流程的最后一步永远是类型回归。

FIND 和 SEARCH 有什么区别，用哪个？

两个都是找字符位置，区别在于 FIND 区分大小写、不支持通配符，SEARCH 不区分大小写、支持问号星号通配符。日常清洗找分隔符、找括号，用 FIND 就行，速度还略快。SEARCH 适合模糊定位：知道某个词但记不清大小写，或者要找以什么开头、以什么结尾的位置。另外两者都只找首次出现的位置，要找第二次出现的位置，得配合起始位置参数，用第一次找到的位置加一当起点再找一次。

TEXTJOIN 和连接符能不能互相替代？

基本可以，但 TEXTJOIN 三个优势太明显：一是一次写一整片区域不用逐个点格子，二是分隔符只写一次，三是自动跳过空格子。老版本 Excel 和老版本 WPS 没有这个函数，做出来的表要发给别人用，兼容性得考虑。如果你的环境都是新版本，TEXTJOIN 几乎在所有合并场景里都应当是首选。