Excel最强“神组合”!TEXTJOIN + TEXTSPLIT,90%的人还不知道这样用

在 Excel 365 和 Excel 2021 中，TEXTJOIN 与 TEXTSPLIT 是一对功能互补的文本处理函数。前者负责合并文本，后者负责拆分文本。当两者结合使用时，可以轻松实现数据的归一化整理、行列转换、复杂文本解析等高级操作。本文将通过详细示例，系统讲解这对黄金组合的实战技巧。

一、函数基础回顾

1. TEXTJOIN – 合并文本

语法

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter：分隔符，如逗号、顿号、换行符等。

• ignore_empty：是否忽略空单元格（TRUE 忽略，FALSE 保留）。

• text1, text2,…：要合并的文本，可以是单元格、区域或数组。

作用：将多个文本字符串用指定分隔符连接成一个字符串。

2. TEXTSPLIT – 拆分文本

语法

TEXTSPLIT(text, [col_delimiter], [row_delimiter], [ignore_empty], [pad_with])

• text：要拆分的文本。

• col_delimiter：列分隔符（按此拆分到不同列）。

• row_delimiter：行分隔符（按此拆分到不同行）。

• ignore_empty：是否忽略空值（TRUE 忽略，FALSE 保留，默认 FALSE）。

• pad_with：当拆分后行列数不规则时，用于填充缺失单元格的值（默认为 #N/A）。

作用：根据指定的分隔符将文本拆分成多行或多列，返回动态数组。

二、组合使用的核心思想

TEXTJOIN 与 TEXTSPLIT 组合的本质是 “先合并，再拆分” 或 “先拆分，再合并”，从而改变数据的形状：

• 合并后拆分：将多个区域（可能不连续、多行多列）先合并成一个字符串，再用 TEXTSPLIT 按需求拆分成规范的行或列。适用于数据归一化、逆透视。

• 拆分后合并：先用 TEXTSPLIT 将文本拆成数组，再用 TEXTJOIN 重新组合，可用来修改分隔符、过滤元素等。

两者结合可以完成许多单一函数无法实现的数据变形。

三、典型组合应用场景

场景1：将多行多列数据合并成一列（归一化）

问题：A1:C3 区域中有若干姓名，可能包含空单元格，想将它们全部整理到一列中，忽略空值。

方法：先用 TEXTJOIN 将所有内容用逗号合并，再用 TEXTSPLIT 按逗号拆分成一列。

=TEXTSPLIT(TEXTJOIN(",", TRUE, A1:C3), , ",")

• TEXTJOIN 将所有非空内容用逗号连接成一个字符串（如 “张三,李四,王五”）。

• TEXTSPLIT 的第二个参数（列分隔符）省略，第三个参数（行分隔符）设为逗号，从而将字符串按逗号拆分为一列（纵向）。

效果：无论原始区域有多少行多少列，最终都会得到一列干净的数据，空白自动忽略。

场景2：将多个不连续区域合并后拆分为一列

问题：A2:A5 和 C2:C4 两个区域都有姓名，想合并成一列，同时去除重复值。

公式：

=UNIQUE(TEXTSPLIT(TEXTJOIN("、", TRUE, A2:A5, C2:C4), , "、"))

• TEXTJOIN 将两个区域的所有姓名用顿号合并。

• TEXTSPLIT 按顿号拆分成一列。

• UNIQUE 去重。

优势：避免了使用 VSTACK 需要手动处理空值的问题。

场景3：条件合并后拆分（动态筛选归一化）

问题：A 列是部门，B 列是姓名。需要将所有“销售部”的姓名整理成一列，其他部门忽略。

传统方法：用 FILTER 筛选出符合条件的姓名，但结果可能是多行，如果需要进一步处理（如与其他列合并），直接用 TEXTJOIN 合并再拆分更灵活。

=TEXTSPLIT(TEXTJOIN("、", TRUE, IF(A2:A100="销售部", B2:B100, "")), , "、")

• IF 判断部门，符合条件的返回姓名，否则返回空字符串。

• TEXTJOIN 将姓名用顿号连接（忽略空值）。

• TEXTSPLIT 拆分成一列，得到所有销售部姓名。

说明：此公式利用了 TEXTJOIN 的 ignore_empty 特性，自动跳过不符合条件的空值。

场景4：修改字符串的分隔符

问题：有一列数据，每个单元格内是用逗号分隔的多个项目，现在需要将分隔符改为顿号，并保留原有结构（每个单元格仍保持在一行）。

方法：先用 TEXTSPLIT 拆分成数组，再用 TEXTJOIN 按新分隔符合并。

=TEXTJOIN("、", TRUE, TEXTSPLIT(A2, ","))

将公式向下填充，即可将每个单元格内的逗号改为顿号。

场景5：从合并字符串中提取特定位置的内容

问题：A 列是多个姓名用逗号分隔，需要提取每个单元格中第 2 个姓名。

方法：用 TEXTSPLIT 拆分成行数组，再用 INDEX 或 CHOOSECOLS 提取指定位置的元素。由于 TEXTSPLIT 返回的是单行数组，可使用 INDEX 提取第 n 个。

=INDEX(TEXTSPLIT(A2, ","), 2)

注意：TEXTSPLIT 默认按行返回（横向），因此 INDEX(..., 2) 取第二个元素。若想取第 n 个，公式通用。

场景6：逆透视多列数据（将交叉表转为清单）

问题：有一个二维表格，行是月份，列是产品，单元格是销售额。需要将其转换为“月份、产品、销售额”三列清单。

方法：利用 TEXTJOIN 与 TEXTSPLIT 配合，先合并再拆分，配合 HSTACK 等函数。

公式示例（假设数据在 A1:D5，A2:A5 是月份，B1:D1 是产品）：

=LET(    months, A2:A5,    products, B1:D1,    data, B2:D5,    m, ROWS(months),    p, COLUMNS(products),    monthArr, TEXTSPLIT(TEXTJOIN("、", TRUE, REPT(months & "、", p)), , "、"),    productArr, TEXTSPLIT(TEXTJOIN("、", TRUE, REPT(products, m)), , "、"),    valueArr, TEXTSPLIT(TEXTJOIN("、", TRUE, TOCOL(data)), , "、"),    HSTACK(monthArr, productArr, valueArr))

• REPT 将月份重复 p 次，产品重复 m 次，再用 TEXTJOIN 合并，TEXTSPLIT 拆分成列，即可得到对应的重复序列。

• 销售额用 TOCOL 转为单列，再拆分得到。

• 最后用 HSTACK 横向堆叠成三列。

这种技巧在处理不规则数据透视时非常有用。

场景7：结合 LET 提高可读性

在复杂组合中，推荐使用 LET 函数定义中间结果，使公式更清晰。

示例：将 A2:A100 中的姓名（每个单元格内可能有多个姓名用顿号分隔）全部提取到一列，去重排序。

=LET(    allText, TEXTJOIN("、", TRUE, A2:A100),    splitArr, TEXTSPLIT(allText, , "、"),    col, TOCOL(splitArr, 3),    SORT(UNIQUE(col)))

• TEXTJOIN 将所有内容合并。

• TEXTSPLIT 拆分成单行数组（注意：此时是单行多列）。

• TOCOL 转为单列，忽略空白和错误。

• 最后去重排序。

四、高级技巧与注意事项

1. 处理分隔符冲突

如果原始文本中已经包含分隔符，直接用 TEXTSPLIT 可能会误拆。此时需要选择不会出现在数据中的特殊分隔符（如 |、@@ 等），或先用 SUBSTITUTE 替换原分隔符。

2. 忽略空值的细节

• TEXTJOIN 的 ignore_empty 只影响合并时是否跳过空单元格，不影响合并后字符串中是否出现连续分隔符。

• TEXTSPLIT 的 ignore_empty 参数控制拆分时是否忽略空值（如连续分隔符产生的空值）。建议根据情况设置为 TRUE，避免空项。

3. 动态数组溢出问题

组合公式返回的动态数组可能占用多个单元格，请确保目标区域有足够空白单元格。若出现 #SPILL!，清空下方单元格即可。

4. 版本限制

• TEXTJOIN 需要 Excel 2019 或更高版本。

• TEXTSPLIT 需要 Excel 365（部分版本需更新至 2022 年后）或 Excel 2021 的特定通道。

• 如果使用旧版本，可用 FILTERXML 等替代，但复杂度较高。

5. 性能考量

当处理大量数据时，TEXTJOIN 合并的字符串长度可能超过单元格限制（32767 字符）。此时会返回 #VALUE! 错误，需考虑分批处理或使用 Power Query。

6. 与 TOCOL/TOROW 的搭配

TEXTSPLIT 拆分后默认返回的是单行数组（按列分隔符拆分为多列）或单列数组（按行分隔符拆分为多行）。若需要将其转为单列，通常使用 TOCOL；若需要转为单行，使用 TOROW。

例如，将拆分的单行数组转为一列：

=TOCOL(TEXTSPLIT(TEXTJOIN(",", TRUE, A1:C3), , ","), 3)

TOCOL 的第二个参数 3 表示忽略空白和错误，与 TEXTSPLIT 的 ignore_empty 配合更稳健。

五、常见错误及解决方案

六、总结

TEXTJOIN 与 TEXTSPLIT 的组合是 Excel 365 中数据整理的利器。它们既能实现 多区域归一化，也能完成 分隔符修改、条件提取、二维表逆透视 等复杂任务。掌握这对组合，可以大幅减少对辅助列、VBA 或 Power Query 的依赖。

核心要点：

• TEXTJOIN 负责“收拢”，TEXTSPLIT 负责“展开”。

• 利用 LET 封装中间步骤，提升公式可维护性。

• 结合 TOCOL、UNIQUE、FILTER 等函数，实现更高级的数据处理。

通过不断实践这些组合，你将能更高效地应对日常工作中遇到的各种文本和数据形状转换问题。