excelperfect
有时候，在我们使用公式处理单元格中的内容时，需要将其拆分成数组，以便于进一步处理。如下图1所示，在单元格A1中是带有“;”分隔符的一个字符串，我们想将其拆分成数组：{'a';'1';'b';'2';'c';'3';'d';'4';'e';'5'}。
图1
可以使用下面的数组公式：
=TRIM(MID(SUBSTITUTE(A1,';',REPT('',999)),ROW(INDIRECT('1:'&LEN(A1)-LEN(SUBSTITUTE(A1,';',''))+1))*999-998,999))
它令人惊叹地将单元格A1中由“;”分隔的字符串转换成了字符串数组。
为方便理解，我们将公式中的数值换小一些：
=TRIM(MID(SUBSTITUTE(A1,';',REPT('',10)),ROW(INDIRECT('1:'&LEN(A1)-LEN(SUBSTITUTE(A1,';',''))+1))*10-9,10))
其中：
（1）SUBSTITUTE(A1,';',REPT('',10))
使用10个空格替换单元格A1中的“;”号，得到：
'a 1 b 2 c 3 d 4 e 5'
（2）LEN(A1)-LEN(SUBSTITUTE(A1,';',''))+1
计算单元格A1中除去“;”后有多少个字符，得到：
10
（3）ROW(INDIRECT('1:'&LEN(A1)-LEN(SUBSTITUTE(A1,';',''))+1))
得到一系列连续的数值，最大值等于A1中除去分隔符后的字符数，得到：
{1;2;3;4;5;6;7;8;9;10}
（4）ROW(INDIRECT('1:'&LEN(A1)-LEN(SUBSTITUTE(A1,';',''))+1))*10-9
得到MID函数中提取字符串的起始位置：
{1;11;21;31;41;51;61;71;81;91}
（5）MID(SUBSTITUTE(A1,';',REPT('',10)),ROW(INDIRECT('1:'&LEN(A1)-LEN(SUBSTITUTE(A1,';',''))+1))*10-9,10)
提取字符串，得到：
{'a ';' 1 ';' b ';' 2 ';' c ';' 3 ';' d ';' 4 ';' e';' 5'}
（6）将上述提取的字符串传递给TRIM函数，去掉开头和结尾的空格，得到字符串数组：
{'a';'1';'b';'2';'c';'3';'d';'4';'e';'5'}
如下图2所示。
图2
示例公式中，之所以取数字999，是为了确保单元格中字符串太长时都能够正确提取出字符串数组。例如，本例中，所取数字最小为10，否则不能完整地提取出字符串数组。
综上，这类问题解决方案的通用公式如下：
=TRIM(MID(SUBSTITUTE(单元格引用,分隔符,REPT(' ',一个足够大的数m)),n*m-(m-1),m))
其中，n代表返回数组中的第n个元素。为了返回一系列元素，可以将n使用能够生成连续数字的公式替换，如示例所示。
这个公式可以使用在其他公式中，以便对生成的数组进一步处理，满足相应的需求。
联系客服
