Excel 中超强大的文本处理函数 ——TEXTSPLIT

Hi,我是偏爱函数公式,爱用 Excel 图表管理仓库的大叔 Mr 赵~

今天咱们一起学习一个超级强大的文本拆分函数,TEXTSPLIT 函数。这个函数目前仅在 Officel 365 版本中可以使用,它的语法是:

=TEXTSPLIT (需要拆分的文本,列分隔符,[行分隔符],[是否忽略空单元格],[是否区分大小写],[出错时填充的值])

接下来,我们就通过 5 个既常见又实用的文本拆分案例,一起认识它有多强大~

1、统计人数

如下图,B 列的名单是以顿号作为连接符连接的一串姓名,需要统计每行的人数。

在 C3 单元格输入如下公式:

=COUNTA(TEXTSPLIT(B3,"、"))

首先用 TEXTSPLIT 函数以顿号「、」作为列分隔符拆分当前的名单,生成一行数组;

然后用 COUNTA 函数统计这个数组中有多少个元素,结果就是人数。

2、计算最大连胜次数

如下图 B3 单元格一串文本是多轮比赛的成绩,需要统计最大连胜了几次

在 C3 单元格输入如下公式:

=MAX(LEN(TEXTSPLIT(B3,{"负", "平"})))

公式中的 {"负","平"},是由字符 "负" 和 "平" 组成的一个常量数组。

我们可以按常量数组的形式将多种分隔符写到一起,再用 TEXTSPLIT 函数拆分文本,结果生成如下图所示的一行数组:

然后用 LEN 函数统计数组每个元素的字符个数,最后用 MAX 函数求出字符个数的最大值,结果就是最大连胜次数。

3、统计缺勤名单

如下图 B3 单元格是公司人员名单,C3 单元格是实际参加会议的人员,需要统计缺勤的名单。

在 D3 单元格输入如下公式:

=TEXTJOIN("、",,TEXTSPLIT(B3,TEXTSPLIT(C3,"、"),"、",1))

公式中的 TEXTSPLIT (C3,,"、") 部分是用 TEXTSPLIT 函数以顿号「、」作为行分隔符,将 C3 单元格的文本拆分成 1 列数组:

然后以这列数组作为列分隔符,以顿号作为行分隔符,用 TEXTSPLIT 函数拆分 B3 单元格的字符串,结果如下图所示:

最后用 TEXTJOIN 函数以顿号作为连接符,连接数组中的每个元素,生成缺勤名单。

4、提取文本中的数字求和

比如 B3 单元格中,是一串由货物品称和金额组成的文本,需要提取金额再求和。

在 D3 单元格输入如下公式:

=SUM(--TEXTSPLIT(B3,,TEXTSPLIT(B3,".",ROW(1:10)-1,1),1))

公式中 ROW (1:10)-1 生成一组 0 到 9 的序列值,

然后用 TEXTSPLIT 函数,分别以小数点作为列隔符,以 0 到 9 的数字作为行分隔符,忽略空白拆分 B3 单元格的文本,结果后成一列货物名称:

再用 TEXTSPLIT 函数,以这列名称作为行分隔符,忽略空白拆分 B3 单元格的文本,结果生成一列数字:

最后用两个负号,将这列数值转化成数值类型,再用 SUM 函数求和,就得到了金额之和,结果如下图 D3 单元格所示:

5、拆分文本成多行多列

如下图,B3 单元格的是由姓名和数字连接起来的一串文本,需要将姓名和数字拆分成两列。

在 D3 单元格输入如下公式:

=LET(a,TEXTSPLIT(B3,,ROW(1:10)-1,1),HSTACK(a,TEXTSPLIT(B3,,a,1)))

公式中 ROW (1:10)-1 生成一组 0 到 9 的序列值,

然后用 TEXTSPLIT 函数,以 0 到 9 的数字作为行分隔符,忽略空白拆分 B3 单元格的文本,生成一列姓名:

再用 LET 函数将这列姓名赋值给变量 a,用 TEXTSPLIT 函数,以这列姓名作为行分隔符,忽略空白拆分 B3 单元格的文本,得出一列数字:

最后用 HSTACK 函数,将得到的姓名和数量连接成多行两列的数组,得到最终结果:

6、写在最后

本文通过以上 5 个案例,展示了 TEXTSPLIT 函数强大的文本拆分功能:

首先用 TEXTSPLIT 函数按指定的分隔符将文本拆分到行和列,结果以数组形式返回,然后与其它函数嵌套使用,应用到实际场景中。

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:赵骄阳

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

文章价值:
人打分
有价值还可以无价值
置顶评论
    热门评论
      文章发布时间太久,仅显示热门评论
      全部评论
      一大波评论正在路上
        取消发送
        软媒旗下人气应用

        如点击保存海报无效,请长按图片进行保存分享