将一个文本拆分为多列是非常常见的需求。在Excel中,很多人习惯于使用“分列”功能。但是这个功能有很大的局限:作为一个需要手工操作的功能,它不能帮助我们建立自动化的处理方案。
如果是一个复杂的数据处理场景,可以考虑使用Power Query。但是,如果是作为Excel的一个简单处理需求,就需要使用公式了。根据拆分场景的不同,需要使用不同的公式。
注:本文给出的公式大部分都需要在新版本Excel或者Office 365中使用。场景
下面是我们列出的一些常见场景:
No.
场景
示例
1
分隔符:唯一
abc,d,f5
2
分隔符:多个,拆分多列(或多行)
abc,d;ef;
3
分隔符:多个,拆分多行多列
a,100,85;b,90,92;c,87,100
4
位置:
abcdefghijklmn
5
字符数:拆分一次
abcdefghijklmn
6
字符数:拆分多次
abcdefghijklmn
7
中文到英文(数字)
衬衫258
8
英文(数字)到中文
abc回归线
9
英文到数字
abc123
10
数字到英文
123abc
11
数字到非数字
下面分别就每种场景中给出相应的拆分公式。按分隔符拆分
这是最常见的一种场景,在Excel中的分列功能主要就是实现了这种场景。我们就可以使用TEXTSPLIT函数完成分拆的工作。
最典型的情况就是:
场景1. 唯一分隔符
比如,有一个文本:
abc,d,f5
中间是用“,”隔开的,需要拆成三列:
=TEXTSPLIT(C2,",")
这个公式不需要过多解释了。
场景2. 多个分隔符,拆分为一行
例如,我们有文本:
abc,d;ef;hijk!lmn
其中的
",",";","!"
都属于分隔符,需要根据它们将文本分成多列:
可以使用公式:
=TEXTSPLIT(C5,{",",";","!"})
需要强调一下,在这两种场景中,都可以将原文本拆分为多行,TEXTSPLIT函数可以设置将这些分隔符作为行分隔或者列分隔。
场景3. 拆分为多行多列
假设文本如下:
a,100,85;b,90,92;c,87,100
需要根据“,”拆分为列,根据";"拆分为行,
可以使用公式:
=TEXTSPLIT(C7,",",";")场景4. 根据位置拆分
这种场景很简单,就是将给定文本按照某个位置一分为二:
这里用不到新函数,只要LEFT和RIGHT函数即可:
=LEFT(C2,2)=RIGHT(C2,LEN(C2)-2+1)
当然,如果希望用一个函数同时得到两个结果,需要用到LET函数:
=LET( position, 2, text, C2, leftpart, LEFT(text,position), rightpart, RIGHT(text, LEN(text)-position), HSTACK(leftpart, rightpart))
这个公式本质上还是使用LEFT和RIGHT函数。只不过通过HSTACK函数连接成一个单行数组。
根据字符个数拆分
在这类场景中,需要根据字符个数拆分。
场景5. 按照字符数拆分(拆分1次)
其实跟按照位置拆分一个意思,就是一个简单的LEFT和RIGHT函数应用而已:
=LET( chars, 2, text, C2, leftpart, LEFT(text,chars), rightpart, RIGHT(text, LEN(text)-chars), HSTACK(leftpart, rightpart))
但是,更常见的是拆分多次:
场景6. 按照字符拆分多次
我们需要将原文本按照每两个字符拆分为多列,如果最后不足两个字符,就作为单独一列。
以前,并没有什么好方法,但是现在我们有了最终极的数据处理函数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函数生成了一个1行,n列的数组,n需要根据文本长度计算,生成的方式是使用LAMBDA函数逐个元素循环每行每列,对于给定的行号和列号,用MID函数取出对应子文本。
参看下面示意图的解释:
更加详细解释参看文末视频。
场景7,8. 中英文混排
这也是一种比较常见的场景,可以是中英文混排,也可以是中文和数字混排,这时我们需要拆成中文和英文两列:
这里我们使用的公式是:
拆分中文
=LEFT(C2, LENB(C2)-LEN(C2)
拆分英文和数字
=RIGHT(C2, 2*LEN(C2)-LENB(C2))
如果文本中英文或数字在前,中文在后,就将上面公式的LEFT和RIGHT对调。
我们下面介绍的公式可以不用关心中英文的顺序:
=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))
这个公式,对于任意两种顺序的文本,都可以得到正确结果:
实际上,就是用了一个ISNUMBER函数来判断是数字在前还是中文在前,然后分别做了处理而已。
更加详细解释参看文末视频。
场景9,10,11. 数字到非数字场景
这个场景涵盖了更普遍的场景,包括英文数字混排,中文数字混排等,例如:
abc123,
123abc
ab12cf34de5gh9
回归线12Excel34Power
我们需要将它们都拆分开:
abc 123
123 abc
ab 12 cf 34 de 5 gh 9
回归线 12 Excel 34 Power
应该说这是一个艰巨的任务,离不开最强大的MAKEARRAY函数。
如果你不想了解原理,就直接使用这里给出的公式,
=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)
解释起来比较复杂,详细解释请看文末视频。下面的图示是一个原理性的解释:
关注公众号,点击底部菜单:知识库,点击客服。联系客服,获得本文所有公式代码。
本文介绍的函数使用技巧,节选自新课程:
《Office 365中的自定义函数》
这门课程中详细介绍了Office 365中自定义函数的方法和技巧。课程重点介绍LAMBDA函数及其伴侣函数的使用,使你的数据处理能力更上一层楼。
联系客服