将带分隔符的字符串拆分成数组的Excel通用公式

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使用能够生成连续数字的公式替换，如示例所示。

这个公式可以使用在其他公式中，以便对生成的数组进一步处理，满足相应的需求。