設(shè)置
  • 日夜間
    隨系統(tǒng)
    淺色
    深色
  • 主題色

Excel 中超強(qiáng)大的文本處理函數(shù) ——TEXTSPLIT

秋葉Excel 2023/10/28 19:04:05 責(zé)編:夢澤

Hi,我是偏愛函數(shù)公式,愛用 Excel 圖表管理倉庫的大叔 Mr 趙~

今天咱們一起學(xué)習(xí)一個超級強(qiáng)大的文本拆分函數(shù),TEXTSPLIT 函數(shù)。這個函數(shù)目前僅在 Officel 365 版本中可以使用,它的語法是:

=TEXTSPLIT (需要拆分的文本,列分隔符,[行分隔符],[是否忽略空單元格],[是否區(qū)分大小寫],[出錯時填充的值])

接下來,我們就通過 5 個既常見又實(shí)用的文本拆分案例,一起認(rèn)識它有多強(qiáng)大~

1、統(tǒng)計人數(shù)

如下圖,B 列的名單是以頓號作為連接符連接的一串姓名,需要統(tǒng)計每行的人數(shù)。

在 C3 單元格輸入如下公式:

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

首先用 TEXTSPLIT 函數(shù)以頓號「、」作為列分隔符拆分當(dāng)前的名單,生成一行數(shù)組;

然后用 COUNTA 函數(shù)統(tǒng)計這個數(shù)組中有多少個元素,結(jié)果就是人數(shù)。

2、計算最大連勝次數(shù)

如下圖 B3 單元格一串文本是多輪比賽的成績,需要統(tǒng)計最大連勝了幾次。

在 C3 單元格輸入如下公式:

=MAX(LEN(TEXTSPLIT(B3,{"負(fù)", "平"})))

公式中的 {"負(fù)","平"},是由字符 "負(fù)" 和 "平" 組成的一個常量數(shù)組。

我們可以按常量數(shù)組的形式將多種分隔符寫到一起,再用 TEXTSPLIT 函數(shù)拆分文本,結(jié)果生成如下圖所示的一行數(shù)組:

然后用 LEN 函數(shù)統(tǒng)計數(shù)組每個元素的字符個數(shù),最后用 MAX 函數(shù)求出字符個數(shù)的最大值,結(jié)果就是最大連勝次數(shù)。

3、統(tǒng)計缺勤名單

如下圖 B3 單元格是公司人員名單,C3 單元格是實(shí)際參加會議的人員,需要統(tǒng)計缺勤的名單。

在 D3 單元格輸入如下公式:

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

公式中的 TEXTSPLIT (C3,,"、") 部分是用 TEXTSPLIT 函數(shù)以頓號「、」作為行分隔符,將 C3 單元格的文本拆分成 1 列數(shù)組:

然后以這列數(shù)組作為列分隔符,以頓號作為行分隔符,用 TEXTSPLIT 函數(shù)拆分 B3 單元格的字符串,結(jié)果如下圖所示:

最后用 TEXTJOIN 函數(shù)以頓號作為連接符,連接數(shù)組中的每個元素,生成缺勤名單。

4、提取文本中的數(shù)字求和

比如 B3 單元格中,是一串由貨物品稱和金額組成的文本,需要提取金額再求和。

在 D3 單元格輸入如下公式:

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

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

然后用 TEXTSPLIT 函數(shù),分別以小數(shù)點(diǎn)作為列隔符,以 0 到 9 的數(shù)字作為行分隔符,忽略空白拆分 B3 單元格的文本,結(jié)果后成一列貨物名稱:

再用 TEXTSPLIT 函數(shù),以這列名稱作為行分隔符,忽略空白拆分 B3 單元格的文本,結(jié)果生成一列數(shù)字:

最后用兩個負(fù)號,將這列數(shù)值轉(zhuǎn)化成數(shù)值類型,再用 SUM 函數(shù)求和,就得到了金額之和,結(jié)果如下圖 D3 單元格所示:

5、拆分文本成多行多列

如下圖,B3 單元格的是由姓名和數(shù)字連接起來的一串文本,需要將姓名和數(shù)字拆分成兩列。

在 D3 單元格輸入如下公式:

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

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

然后用 TEXTSPLIT 函數(shù),以 0 到 9 的數(shù)字作為行分隔符,忽略空白拆分 B3 單元格的文本,生成一列姓名:

再用 LET 函數(shù)將這列姓名賦值給變量 a,用 TEXTSPLIT 函數(shù),以這列姓名作為行分隔符,忽略空白拆分 B3 單元格的文本,得出一列數(shù)字:

最后用 HSTACK 函數(shù),將得到的姓名和數(shù)量連接成多行兩列的數(shù)組,得到最終結(jié)果:

6、寫在最后

本文通過以上 5 個案例,展示了 TEXTSPLIT 函數(shù)強(qiáng)大的文本拆分功能:

首先用 TEXTSPLIT 函數(shù)按指定的分隔符將文本拆分到行和列,結(jié)果以數(shù)組形式返回,然后與其它函數(shù)嵌套使用,應(yīng)用到實(shí)際場景中。

本文來自微信公眾號:秋葉 Excel (ID:excel100),作者:趙驕陽

廣告聲明:文內(nèi)含有的對外跳轉(zhuǎn)鏈接(包括不限于超鏈接、二維碼、口令等形式),用于傳遞更多信息,節(jié)省甄選時間,結(jié)果僅供參考,IT之家所有文章均包含本聲明。

相關(guān)文章

關(guān)鍵詞:Excel教程Excel學(xué)院

軟媒旗下網(wǎng)站: IT之家 最會買 - 返利返現(xiàn)優(yōu)惠券 iPhone之家 Win7之家 Win10之家 Win11之家

軟媒旗下軟件: 軟媒手機(jī)APP應(yīng)用 魔方 最會買 要知