办公软件Excel 文本处理全攻略五(截取、合并、替换、清洗…)

脏数据终结者：让Excel像剪刀一样精准处理文本

一、LEFT / RIGHT / MID —— 截取字符三兄弟

语法
=LEFT(文本, 提取位数) — 从左截取
=RIGHT(文本, 提取位数) — 从右截取
=MID(文本, 起始位置, 提取位数) — 从中间截取

详细使用分析

LEFT / RIGHT 最常用场景

MID 灵活取中间

=MID("Excel函数大全", 6, 2) → 函数

身份证号提取出生年月
=MID(A2, 7, 8) → 19901015
再包一层 TEXT：=TEXT(MID(A2,7,8), "0000-00-00")

⚠️ 注意
位数超过文本长度会返回全部文本，不报错；负数或0会报 #VALUE!

二、LEN / LENB —— 字符长度

语法
=LEN(文本) — 字符个数
=LENB(文本) — 字节个数（中文字符占2字节）

详细使用分析

LEN 典型应用

① 判断是否为空
=LEN(A1)=0 等价于 A1=""，但能识别看起来为空的有空格单元格

② 文本长度校验
=IF(LEN(A2)=18, "合法身份证", "长度不对")

③ 结合 LEFT/RIGHT 动态截取
=LEFT(A2, LEN(A2)-3) — 去掉最后3个字符

LENB 的中文识别价值

=LENB(A1)-LEN(A1) → 中文字符个数（中→1，英→0）

三、TRIM —— 删除多余空格

语法
=TRIM(文本)

详细使用分析

能删除：

• 单词之间多个空格保留1个

• 首尾所有空格

不能删除：

• 中文全角空格（需用 SUBSTITUTE + CHAR(41377)）

实战对比

⚠️ 坑点提醒
TRIM 不影响单元格真实存储值，建议在公式链最外层使用：
=TRIM(SUBSTITUTE(A1, CHAR(160), " ")) — 删除不可见空格

四、UPPER / LOWER / PROPER —— 大小写转换

语法
=UPPER(文本) — 全大写
=LOWER(文本) — 全小写
=PROPER(文本) — 首字母大写

详细使用分析

UPPER / LOWER 常规用法

PROPER 的神奇之处

=PROPER("john smith") → John Smith
=PROPER("excel函数") → Excel函数（会误把中文后英文大写）

⚠️ 注意
PROPER 遇到数字或特殊符号后的字母会首字母大写：
=PROPER("excel2019函数") → Excel2019函数

五、CONCATENATE 与 & —— 合并文本

语法
=CONCATENATE(文本1, [文本2], …)
=文本1 & 文本2 & 文本3

详细使用分析

✅ 强烈推荐用 &，原因如下：

经典例子

=A2&"年"&B2&"月"&C2&"日" → 2024年8月20日
=IF(E2>=60, "及格", "不及格")&"：得分"&E2

数组合并技巧（Excel 2019+）
=TEXTJOIN("-", TRUE, A1:A5) — 用分隔符合并区域，忽略空值

六、SUBSTITUTE —— 按内容替换

语法
=SUBSTITUTE(文本, 旧文本, 新文本, [替换第几次出现])

详细使用分析

核心特点：只认内容，不认位置

四大经典场景

① 删除特定字符
=SUBSTITUTE(A1, "-", "") → 去掉所有连字符

② 替换第N次出现
=SUBSTITUTE("A_B_C_D", "_", "-", 2) → A_B-C_D

③ 计算字符出现次数
=LEN(A1)-LEN(SUBSTITUTE(A1, "，", "")) → 多少个中文逗号

④ 清理隐藏换行符
=SUBSTITUTE(A1, CHAR(10), "，") → 换行转逗号

与 REPLACE 的选择题

• 知道删什么内容 → SUBSTITUTE

• 知道哪个位置 → REPLACE

七、REPLACE —— 按位置替换

语法
=REPLACE(旧文本, 起始位置, 替换长度, 新文本)

详细使用分析

身份证号脱敏（保留前6后4）
=REPLACE(A2, 7, 8, "********")
→ 11010119900307663X → 110101********63X

手机号隐藏中间4位
=REPLACE(B2, 4, 4, "****") → 138****5678

固定位置插入字符
=REPLACE(A2, 3, 0, "-") — 长度为0时相当于插入

⚠️ SUBSTITUTE vs REPLACE 速断表

八、FIND / SEARCH —— 查找位置

语法
=FIND(查找文本, 源文本, [起始位置]) — 区分大小写
=SEARCH(查找文本, 源文本, [起始位置]) — 不区分大小写

详细使用分析

返回值：首次出现的位置数字，找不到返回 #VALUE!

经典嵌套用法

① MID + FIND 动态提取
=MID(A2, FIND("@", A2)+1, 99) → 提取邮箱域名

② 判断是否包含某词
=IF(ISNUMBER(FIND("Excel", A2)), "包含", "不包含")

③ 多层拆解（提取姓名+手机号）
=LEFT(A2, FIND(" ", A2)-1) — 取空格前的内容

④ 通配符查找（仅SEARCH）
=SEARCH("?B?", A2) — 查找如“ABE”“CBC”模式的三个字符

九、TEXT —— 格式化数字为文本

语法
=TEXT(数值, 格式代码)

详细使用分析

让人又爱又恨的函数

• 作用：把数字变成指定样式的文本

• 坑：结果是文本，无法参与数学运算

格式代码常用表

日期合并文本（必备技巧）
="截止"&TEXT(A1,"yyyy年mm月dd日")&"，销售额为"&B1
不用 TEXT 会显示数字：例如 45423 这种日期序列值

⚠️ 常见错误

综合实战：一套完整的文本清洗流程

场景：原始数据 " 姓名：张三(A级) 电话：138****1234 "

清洗目标：提取姓名和等级，格式为 张三_A级

步骤公式

一条公式写完（进阶）
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"："," "),"(","_"), FIND(" ",A2)+1, FIND("_",A2)-FIND(" ",A2)-1))

速查总结表（建议收藏）