文本处理——让 AI 帮你拆分、合并、清洗数据
第5章：文本处理函数
现实中的数据很少是干净整洁的。从系统导出的客户信息可能带着多余空格，身份证号藏在一大段描述里，手机号需要脱敏，几十列的字段需要拼成一句话……文本函数就是处理这些"脏数据"的专用工具。结合 AI，即使是复杂的文本提取逻辑，也能快速转化为公式。
文本函数的使用场景
文本函数在以下场景中使用频率极高：
- 身份证提取：从18位身份证号提取出生年月、性别、籍贯省份
- 手机号脱敏：把 138****8888 这样的格式处理为合规的脱敏显示
- 姓名/地址拆分：把"张三 销售部 北京"这样的混合字段拆成独立列
- 数据清洗：去除多余空格、清理特殊字符、统一大小写格式
- 字段拼接：把姓名+部门+职位拼成"张三（销售部·经理）"这样的展示文字
- 格式转换：把数字转成货币格式文字、把日期转成指定格式字符串
文本函数可以分为四大类：提取、清洗、合并、转换。下面逐类讲解。
ℹ️ Note
关于AI辅助文本公式：
文本处理公式是 AI 表现极好的另一个领域。原因是：你可以直接把样例数据给 AI（"输入是 '广东省深圳市南山区科技园路1号'，我要提取省份"），AI 能看懂你的意图并写出对应的公式。本章每个案例都配有可直接使用的 AI Prompt。
提取函数：LEFT / RIGHT / MID / FIND / SEARCH
函数语法对比
| 函数 | 作用 | 语法 | 特点 |
|---|---|---|---|
| LEFT | 从左侧提取N个字符 | =LEFT(文本, 字符数) | 固定从左数 |
| RIGHT | 从右侧提取N个字符 | =RIGHT(文本, 字符数) | 固定从右数 |
| MID | 从中间提取 | =MID(文本, 起始位置, 字符数) | 指定起点和长度 |
| FIND | 查找字符位置（区分大小写） | =FIND(查找内容, 文本, [起始位置]) | 返回数字（位置），区分大小写 |
| SEARCH | 查找字符位置（不区分大小写） | =SEARCH(查找内容, 文本, [起始位置]) | 支持通配符 * 和 ? |
FIND 和 SEARCH 本身不提取内容，而是返回字符的位置（第几个字符），通常与 LEFT/RIGHT/MID 组合使用来实现动态提取。
6个提取案例
提取案例1：从身份证提取出生年月（MID） 场景：A列是18位身份证号码，B列提取出生年份，C列提取出生月份，D列提取完整出生日期（格式：1990-06-15）
从身份证号提取生日
提取案例2：从邮件地址提取用户名（LEFT+FIND） 场景：A列是邮件地址（如 [email protected]），B列提取@前面的用户名（zhangsan）
提取案例3：从完整地址提取省份（LEFT） 场景：A列是标准格式地址（如"广东省深圳市南山区..."），B列提取省份（"广东省"，3个字）
提取案例4：提取括号内的内容（MID+FIND） 场景：A列内容如"年度报告（2024版）"、"产品说明书（V3.2修订）"，B列提取括号内的内容（"2024版"、"V3.2修订"）
提取案例5：从产品编号提取类别代码 场景：A列产品编号格式为"类别-序号-年份"，如"ELE-001-2024"，B列提取类别代码（"ELE"），C列提取序号（"001"），D列提取年份（"2024"）
提取案例6：AI帮你写复杂提取公式 场景：实际数据格式混乱，不是标准结构，需要让 AI 看到样例数据直接给公式
清洗函数：TRIM / CLEAN / SUBSTITUTE / REPLACE
函数说明
| 函数 | 作用 | 语法 | 典型场景 |
|---|---|---|---|
| TRIM | 去除首尾空格，压缩中间连续空格为单个 | =TRIM(文本) | 系统导出数据清洗 |
| CLEAN | 删除不可打印字符（换行符、制表符等） | =CLEAN(文本) | 从网页或PDF粘贴的数据 |
| SUBSTITUTE | 替换指定文字（可指定替换第几次出现） | =SUBSTITUTE(文本, 旧文本, 新文本, [第N次]) | 批量替换词汇 |
| REPLACE | 按位置替换（第N位开始的M个字符） | =REPLACE(文本, 起始位置, 字符数, 新文本) | 手机号脱敏、部分屏蔽 |
清洗案例1：去除多余空格（TRIM） 场景：从系统导出的员工姓名数据，有的在姓名前后有空格，有的中间有多个连续空格，导致查找和匹配失败
清洗案例2：批量替换关键词（SUBSTITUTE） 场景：A列是产品描述，其中含有过时的品牌名"老品牌"，需要批量替换为"新品牌"，同时去掉描述中的所有空格
清洗案例3：手机号脱敏（REPLACE） 场景：A列是完整手机号（如13812345678），B列要显示脱敏版本（138****5678），隐藏中间4位
清洗案例4：清理特殊字符（CLEAN） 场景：从 PDF 或网页粘贴的数据，含有换行符、制表符等不可见字符，导致 VLOOKUP 匹配失败
我有一列数据，部分样本如下：
" 张三 销售部 13812345678 "
"李四 市场部 13987654321 "
" 王五 技术部 13666666666"
我想拆成三列：姓名（A列）、部门（B列）、手机号（C列）。
数据问题：空格不规则，间隔可能是1个或多个空格，有的有前后空格。
请给出清洗和拆分的Excel公式方案（不用VBA）。
合并函数：CONCATENATE / CONCAT / TEXTJOIN / & 符号
四种拼接方式对比
| 方式 | 语法 | 特点 | 推荐场景 |
|---|---|---|---|
| & 符号 | ="张"&"三"&A2 | 最简洁，逐个拼接 | 少量字段拼接 |
| CONCAT | =CONCAT(A2:C2) | 可引用范围，无分隔符 | 连续列合并 |
| TEXTJOIN | =TEXTJOIN("分隔符",TRUE,范围) | 可忽略空单元格，自动加分隔符 | 多行/多列合并为一个字符串 |
| CONCATENATE | =CONCATENATE(A2,B2,C2) | 旧版本函数，逐参数拼接 | 兼容旧版Excel时使用 |
合并案例1：姓名+部门拼接展示文字 场景：A列姓名，B列部门，C列职位，D列要显示"张三（销售部·经理）"格式的展示文字
合并案例2：日期格式重组 场景：A列年份（如2024），B列月份（如3），C列日期（如5），D列要显示"2024年03月05日"（月日补零）
合并案例3：多行合并为一行（TEXTJOIN） 场景：A列是同一客户的多个联系方式（分散在多行），需要在B列把同一客户的所有联系方式合并到一个单元格，用逗号分隔
合并案例4：动态生成SQL语句（高级用法） 场景：有一批需要批量导入数据库的数据，A列ID，B列姓名，C列部门，用 Excel 公式自动生成 INSERT SQL 语句
转换函数：UPPER / LOWER / PROPER / TEXT / VALUE
函数说明
| 函数 | 作用 | 示例 |
|---|---|---|
| UPPER | 全部转大写 | =UPPER("hello") → "HELLO" |
| LOWER | 全部转小写 | =LOWER("HELLO") → "hello" |
| PROPER | 首字母大写（每个单词） | =PROPER("hello world") → "Hello World" |
| TEXT | 将数字/日期转成指定格式的文字 | =TEXT(1234.5,"#,##0.00") → "1,234.50" |
| VALUE | 将文字形式的数字转成数值 | =VALUE("1,234") → 1234 |
转换案例1：英文大小写统一 场景：A列是客户公司英文名，格式混乱（有全大写、全小写、混合大小写），需要统一转为"每个单词首字母大写"的标准格式
转换案例2：数字转货币格式文字（TEXT） 场景：A列是金额数字（如 1234567.8），B列要显示为"¥1,234,567.80"格式的文字（用于生成报告文字描述）
转换案例3：日期转指定格式字符串 场景：A列是 Excel 日期值，需要转成不同格式的文字（用于生成报告标题、文件名等）
转换案例4：文本数字转数值（VALUE） 场景：从系统导出的数据，金额列被识别为文本（左对齐，不能求和），需要转换为可计算的数值
LEN 与 LENB：字符数统计
LEN 和 LENB 是两个简单但非常实用的辅助函数：
LEN(文本) → 返回字符数（中英文都算1个字符）
=LEN("Hello") = 5
=LEN("你好") = 2
LENB(文本) → 返回字节数（中文算2字节，英文算1字节）
=LENB("Hello") = 5
=LENB("你好") = 4
判断是否含中文字符：
=LENB(A2)>LEN(A2) ← TRUE说明含有中文（中文的字节数>字符数）
统计文本中的中文字符数：
=LENB(A2)-LEN(A2)
统计英文字符数（在中英混合文本中）：
=2*LEN(A2)-LENB(A2)
实用场景：超字数提醒
场景：A列是广告文案，字数不能超过30个字（中英混合按字符数计）
B2 超字数提醒：
=IF(LEN(A2)>30, "超出"&(LEN(A2)-30)&"字", "符合")
场景：判断手机号是否为11位
=IF(LEN(A2)=11, "正确", "号码位数错误（当前"&LEN(A2)&"位）")
综合案例：整理一份杂乱的客户信息表
现实中，文本函数很少单独使用，而是多个组合叠加。下面用一个完整案例演示从杂乱数据到标准化数据的全流程。
原始数据问题描述
你从 CRM 系统导出了一份客户信息表，A列的格式是：
" 张三/销售部/经理/ 13812345678/ [email protected] "
"李四 / 市场部 / 专员/13987654321/[email protected]"
" 王五/技术部/总监/ 13666666666 / [email protected] "
问题清单：前后有空格、字段间距不统一、分隔符是/、需要拆成5列。
文本函数速查表
| 类别 | 函数 | 核心用法 | 典型公式 |
|---|---|---|---|
| 提取 | LEFT | 从左取N字符 | =LEFT(A2, 3) |
| RIGHT | 从右取N字符 | =RIGHT(A2, 4) | |
| MID | 从指定位置取N字符 | =MID(A2, 7, 4) | |
| FIND | 找字符位置（区分大小写） | =FIND("@", A2) | |
| SEARCH | 找字符位置（不区分+通配符） | =SEARCH("abc", A2) | |
| 清洗 | TRIM | 去多余空格 | =TRIM(A2) |
| CLEAN | 去不可打印字符 | =CLEAN(A2) | |
| SUBSTITUTE | 按内容替换 | =SUBSTITUTE(A2,"旧","新") | |
| REPLACE | 按位置替换 | =REPLACE(A2,4,4,"****") | |
| 合并 | & 符号 | 拼接字符串 | =A2&"·"&B2 |
| CONCAT | 范围合并（无分隔符） | =CONCAT(A2:C2) | |
| TEXTJOIN | 用分隔符合并（可忽略空） | =TEXTJOIN("、",TRUE,A2:A10) | |
| 转换 | UPPER/LOWER | 英文大小写转换 | =UPPER(A2) |
| PROPER | 首字母大写 | =PROPER(A2) | |
| TEXT | 数字/日期→格式化文字 | =TEXT(A2,"¥#,##0.00") | |
| VALUE | 文字数字→数值 | =VALUE(A2) | |
| LEN / LENB | 统计字符数/字节数 | =LEN(A2) |
✅ Tip
本章核心总结：
提取固定位置内容用 LEFT/RIGHT/MID，提取分隔符前后内容用 FIND/SEARCH 配合 MID/LEFT。
数据清洗必备组合：=TRIM(CLEAN(A2))，能处理绝大多数空格和特殊字符问题。
合并字段：少量用 & 符号，多列用 CONCAT，带分隔符或需忽略空单元格用 TEXTJOIN。
TEXT 函数将数字转成展示文字，VALUE 函数将文字数字转成可计算数值，两者功能相反。
遇到复杂格式，把 3-5 行真实数据样本 + 期望输出直接给 AI，比描述格式更高效。
上一章 ← 第4章：条件计算函数 下一章 第6章：日期与时间函数 →