【原】史上最全的Excel文本拆分公式大全！附帶詳細(xì)解釋

ExcelEasy 2022-08-26 發(fā)布于北京

展開全文

將一個(gè)文本拆分為多列是非常常見的需求。在Excel中，很多人習(xí)慣于使用“分列”功能。但是這個(gè)功能有很大的局限：作為一個(gè)需要手工操作的功能，它不能幫助我們建立自動(dòng)化的處理方案。

如果是一個(gè)復(fù)雜的數(shù)據(jù)處理場(chǎng)景，可以考慮使用Power Query。但是，如果是作為Excel的一個(gè)簡(jiǎn)單處理需求，就需要使用公式了。根據(jù)拆分場(chǎng)景的不同，需要使用不同的公式。

注：本文給出的公式大部分都需要在新版本Excel或者Office 365中使用。場(chǎng)景

下面是我們列出的一些常見場(chǎng)景：

No.

場(chǎng)景

示例

分隔符：唯一

abc,d,f5

分隔符：多個(gè)，拆分多列（或多行）

abc,d;ef;

分隔符：多個(gè)，拆分多行多列

a,100,85;b,90,92;c,87,100

位置：

abcdefghijklmn

字符數(shù)：拆分一次

abcdefghijklmn

字符數(shù)：拆分多次

abcdefghijklmn

中文到英文（數(shù)字）

襯衫258

英文（數(shù)字）到中文

abc回歸線

英文到數(shù)字

abc123

數(shù)字到英文

123abc

數(shù)字到非數(shù)字

下面分別就每種場(chǎng)景中給出相應(yīng)的拆分公式。按分隔符拆分

這是最常見的一種場(chǎng)景，在Excel中的分列功能主要就是實(shí)現(xiàn)了這種場(chǎng)景。我們就可以使用TEXTSPLIT函數(shù)完成分拆的工作。

最典型的情況就是：

場(chǎng)景1. 唯一分隔符

比如，有一個(gè)文本：

abc,d,f5

中間是用“,”隔開的，需要拆成三列：

=TEXTSPLIT(C2,",")

這個(gè)公式不需要過多解釋了。

場(chǎng)景2. 多個(gè)分隔符，拆分為一行

例如，我們有文本：

abc,d;ef;hijk!lmn

其中的

",",";","!"

都屬于分隔符，需要根據(jù)它們將文本分成多列：

可以使用公式：

=TEXTSPLIT(C5,{",",";","!"})

需要強(qiáng)調(diào)一下，在這兩種場(chǎng)景中，都可以將原文本拆分為多行，TEXTSPLIT函數(shù)可以設(shè)置將這些分隔符作為行分隔或者列分隔。

場(chǎng)景3. 拆分為多行多列

假設(shè)文本如下：

a,100,85;b,90,92;c,87,100

需要根據(jù)“,”拆分為列，根據(jù)";"拆分為行，

可以使用公式：

=TEXTSPLIT(C7,",",";")場(chǎng)景4. 根據(jù)位置拆分

這種場(chǎng)景很簡(jiǎn)單，就是將給定文本按照某個(gè)位置一分為二：

這里用不到新函數(shù)，只要LEFT和RIGHT函數(shù)即可：

=LEFT(C2,2)=RIGHT(C2,LEN(C2)-2+1)

當(dāng)然，如果希望用一個(gè)函數(shù)同時(shí)得到兩個(gè)結(jié)果，需要用到LET函數(shù)：

=LET( position, 2, text, C2, leftpart, LEFT(text,position), rightpart, RIGHT(text, LEN(text)-position), HSTACK(leftpart, rightpart))

這個(gè)公式本質(zhì)上還是使用LEFT和RIGHT函數(shù)。只不過通過HSTACK函數(shù)連接成一個(gè)單行數(shù)組。

根據(jù)字符個(gè)數(shù)拆分

在這類場(chǎng)景中，需要根據(jù)字符個(gè)數(shù)拆分。

場(chǎng)景5. 按照字符數(shù)拆分(拆分1次）

其實(shí)跟按照位置拆分一個(gè)意思，就是一個(gè)簡(jiǎn)單的LEFT和RIGHT函數(shù)應(yīng)用而已：

=LET( chars, 2, text, C2, leftpart, LEFT(text,chars), rightpart, RIGHT(text, LEN(text)-chars), HSTACK(leftpart, rightpart))

但是，更常見的是拆分多次：

場(chǎng)景6. 按照字符拆分多次

我們需要將原文本按照每?jī)蓚€(gè)字符拆分為多列，如果最后不足兩個(gè)字符，就作為單獨(dú)一列。

以前，并沒有什么好方法，但是現(xiàn)在我們有了最終極的數(shù)據(jù)處理函數(shù)MAKEARRAY，就可以使用下面的公式：

=LET( text, C6, chars,2, MAKEARRAY( 1, ROUNDUP(LEN(text)/chars,0), LAMBDA(r,c, MID(text,((c-1)*chars+1),MIN(chars,LEN(text)-(c-1)*chars))) ))

這里我們使用MAKEARRAY函數(shù)生成了一個(gè)1行，n列的數(shù)組，n需要根據(jù)文本長(zhǎng)度計(jì)算，生成的方式是使用LAMBDA函數(shù)逐個(gè)元素循環(huán)每行每列，對(duì)于給定的行號(hào)和列號(hào)，用MID函數(shù)取出對(duì)應(yīng)子文本。

參看下面示意圖的解釋：

更加詳細(xì)解釋參看文末視頻。

場(chǎng)景7，8. 中英文混排

這也是一種比較常見的場(chǎng)景，可以是中英文混排，也可以是中文和數(shù)字混排，這時(shí)我們需要拆成中文和英文兩列：

這里我們使用的公式是：

拆分中文

=LEFT(C2, LENB(C2)-LEN(C2)

拆分英文和數(shù)字

=RIGHT(C2, 2*LEN(C2)-LENB(C2))

如果文本中英文或數(shù)字在前，中文在后，就將上面公式的LEFT和RIGHT對(duì)調(diào)。

我們下面介紹的公式可以不用關(guān)心中英文的順序：

=LET( text,C2, num_hz, LENB(text)-LEN(text), num_en, 2*LEN(text)-LENB(text), f, ISNUMBER(VALUE(LEFT(text,1))), hzpart, IF(f, RIGHT(text, num_hz), LEFT(text,num_hz)), enpart, IF(f,LEFT(text, num_en), RIGHT(text, num_en)), HSTACK(hzpart,enpart))

這個(gè)公式，對(duì)于任意兩種順序的文本，都可以得到正確結(jié)果：

實(shí)際上，就是用了一個(gè)ISNUMBER函數(shù)來判斷是數(shù)字在前還是中文在前，然后分別做了處理而已。

更加詳細(xì)解釋參看文末視頻。

場(chǎng)景9，10，11. 數(shù)字到非數(shù)字場(chǎng)景

這個(gè)場(chǎng)景涵蓋了更普遍的場(chǎng)景，包括英文數(shù)字混排，中文數(shù)字混排等，例如：

abc123，

123abc

ab12cf34de5gh9

回歸線12Excel34Power

我們需要將它們都拆分開：

abc 123

123 abc

ab 12 cf 34 de 5 gh 9

回歸線 12 Excel 34 Power

應(yīng)該說這是一個(gè)艱巨的任務(wù)，離不開最強(qiáng)大的MAKEARRAY函數(shù)。

如果你不想了解原理，就直接使用這里給出的公式，

=LET( text, C2, char, "|", l, LEN(text), a_letter, MID(C4,SEQUENCE(1,l),1), a_isnumber, MAKEARRAY(1,l,LAMBDA(r,c,ISNUMBER(VALUE(INDEX(a_letter,c))))), a_d, MAKEARRAY(1,l,LAMBDA(r,c, IF(c=1, FALSE,XOR(INDEX(a_isnumber,c-1),INDEX(a_isnumber,c))))), a_procletter, MAKEARRAY(1,l, LAMBDA(r,c,IF(INDEX(a_d,c), char & INDEX(a_letter,c),INDEX(a_letter,c)))), proctext, TEXTJOIN("",TRUE,a_procletter), rslt, TEXTSPLIT(proctext, char), rslt)

解釋起來比較復(fù)雜，詳細(xì)解釋請(qǐng)看文末視頻。下面的圖示是一個(gè)原理性的解釋：