Excel中类似分列功能的公式

Excel中类似分列功能的公式

一、固定宽度分列

    在分列时的“文本分列向导”对话框中有两个选项，分别是“固定宽度”和“分隔符号”。假如A1单元格中包含文本“甲乙丙丁戊己”，分列的宽度相同。

    分列的宽度为1个字符时，在B1单元格中输入公式：

    =MID($A$1,COLUMN(A1),1)

    分列的宽度为2个字符时，在B2单元格中输入公式：

    =MID($A$1,COLUMN(A1)*2-1,2)

    分列的宽度为3个字符时，在B3单元格中输入公式：

    =MID($A$1,COLUMN(A1)*3-2,3)

    然后分别向右填充公式，即可得到按不同字符宽度分列后的数据。

   下面的多单元格数组公式将A1单元格中的数据按1-3个字符宽度“分列”到一行多列的内存数组中：

    =MID(A1,COLUMN(INDIRECT("1:"&LEN(A1))),1)

    =MID(A1,COLUMN(INDIRECT("1:"&LEN(A1)))*2-1,2)

    =MID(A1,COLUMN(INDIRECT("1:"&LEN(A1)))*3-2,3)

    选择一个一行多列的区域，如B5:G5，输入上述公式，然后按Ctrl+Shift+Enter，即可得到分列后的数据。

    二、按分隔符号分列

    例如A1单元格中包含用顿号分割的文本“足球、篮球、沙滩排球、羽毛球、网球、乒乓球”，用下面的公式将顿号分隔的数据分列到一行中：

    在某个单元格，如A4单元格输入公式：

    =TRIM(MID(SUBSTITUTE($A$1,"、",REPT(" ",LEN($A$1))),(COLUMN(A1)-1)*LEN($A$1)+1,LEN($A$1)))

    然后拖动填充柄向右填充公式即可。

    下面的多单元格数组公式将A1单元格中的文本“分列”到一个一行多列的内存数组：

    =(TRIM(MID(SUBSTITUTE(A1,"、",REPT(" ",LEN(A1))),LEN(A1)*(COLUMN(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"、",""))+1))-1)+1,LEN(A1))))

    选择一个一行多列的区域，如B6:F6，输入上述公式，然后按Ctrl+Shift+Enter，即可得到分列后的数据。