Excel 文本函数指南
为什么文本函数是你的数据瑞士军刀
现实世界的数据是混乱的。姓名以"姓, 名"的格式出现，地址将街道、城市、省份和邮编挤在一个单元格里，产品代码在其字符位置中嵌入了含义。文本函数就是清理、拆分、组合和提取这些混乱中意义的工具。没有它们，你只能手动编辑成千上万个单元格——有了它们，你只需编写一个公式然后向下拖动。
Excel 的文本函数库非常深厚。本指南涵盖处理 90% 实际文本问题的核心函数，按从简单到高级的顺序组织。
分步操作：清理和重构混乱的联系人数据
场景：你收到一份联系人列表，每个单元格包含"姓, 名 | 公司 | 电话 | 邮箱"——全部在一个列中。你需要五个干净的列：姓、名、公司、电话、邮箱。
第一步——了解数据模式
- 查看 5-10 个样本单元格。确认分隔符是否一致：竖线
|分隔字段，逗号-空格分隔姓和名。 - 注意任何不规则之处：部分条目可能是"公司有限公司"和"公司, 有限公司"——公司名称中的逗号可能会影响提取。确认竖线分隔符是真正的安全分隔符。
第二步——按主分隔符（竖线）拆分
- 在数据右侧插入 5 列。标签为姓、名、公司、电话、邮箱。
- 更简单的方法——分列：选中数据列。数据 > 分列 > 分隔符号 > 勾选其他并输入
|。点击完成。Excel 拆分为 5 列。 - 或使用公式进行动态拆分：
- 公司（C2）：
=TRIM(MID(SUBSTITUTE($A2,"|",REPT(" ",100)), 100, 100)) - 这个 SUBSTITUTE+REPT 技巧将每个分隔符替换为 100 个空格，然后 MID 提取每个分段。TRIM 删除多余空格。
- 提取第 2 段将
,100,100改为,200,100；第 3 段用,300,100；以此类推。
第三步——将姓名字段拆分为姓和名
- 姓：
=LEFT(B2, FIND("，", B2)-1)——逗号前的所有内容。 - 名：
=TRIM(RIGHT(B2, LEN(B2)-FIND("，", B2)-1))——逗号后的所有内容，用 TRIM 去除前导空格。 - 如果数据有中间名，这会提取逗号后的所有内容，优雅地处理中间名。
第四步——清理电话号码
- 你提取的电话号码可能看起来像" 555-0100 "（多余空格）或"(555) 0100"（混合格式）。
- 提取纯数字（Excel 365）：
=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(D2, SEQUENCE(LEN(D2)), 1)), MID(D2, SEQUENCE(LEN(D2)), 1), "")) - 旧版 Excel 使用嵌套 SUBSTITUTE：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(D2),"(",""),")",""),"-","")," ","") - 重新格式化为 138-1234-5678：
=TEXT(纯数字,"000-0000-0000")
关键技巧
- TEXTJOIN 用于组合：
=TEXTJOIN("，", TRUE, B2:B10)用分隔符连接数值并跳过空单元格（TRUE 参数）。比=A2&"，"&B2&"，"&C2干净得多。 - TEXT 用于字符串中数字格式化：
="收入：" & TEXT(B2, "¥#,##0.00")将数字与文本组合时保留格式。不用 TEXT，数字会丢失格式。 - SUBSTITUTE 用于精确替换：
=SUBSTITUTE(A2, "旧", "新")替换所有出现的位置。添加第 4 个参数只替换第 n 次出现：=SUBSTITUTE(A2, "-", "|", 2)仅替换第二个连字符。 - LEN 用于验证：
=IF(LEN(B2)<>11, "无效手机号", "OK")立即捕获格式不正确的电话号码。
常见错误
- 在文本可能不存在时使用 FIND 而不加 IFERROR。
=FIND("@", A2)如果没有 @ 符号会返回 #VALUE!。用 IFERROR 包装：=IFERROR(FIND("@", A2), 0)。 - 忘记 FIND 从 1 开始计数。MID 使用位置 0 会报错。必要时减 1 以保持在位置 1 或以上。
- TRIM 只删除 ASCII 空格（char 32）。网页数据通常包含不间断空格（char 160）。在 TRIM 之前使用
=SUBSTITUTE(A2, CHAR(160), " ")。 - PROPER 产生错误的大写。"MCDONALD"变成"Mcdonald"，"USA"变成"Usa"。需要手动修正专有名词。
进阶技巧
- 提取第 N 个词：
=TRIM(MID(SUBSTITUTE(A2, " ", REPT(" ", LEN(A2))), (N-1)*LEN(A2)+1, LEN(A2)))。N=1 为第一个词，N=2 为第二个，以此类推。 - TEXTSPLIT（Excel 365）：
=TEXTSPLIT(A2, "|")跨列动态拆分分隔字符串。与 TEXTJOIN 结合，无需辅助列即可实现强大重塑操作。 - REPT 制作单元格内可视化指示器：
=REPT("|", B2/10)在单元格内创建条形图。结合条件格式字体颜色，无需图表对象即可快速进行视觉比较。