|
将一个文本拆分为多列是非常常见的需求。在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函数及其伴侣函数的使用，使你的数据处理能力更上一层楼。 |
|
|
