Excel单元格内容拆分：如何批量将单格变成多行显示的方法

编按：如何根据分隔符将各单元格中的内容批量拆分为多行？今天主要来教大家3种方法：PQ拆分法，空格替换提取法，OFFICE365函数法。

如下图所示，这是关于各个部门派出的参会名单，每个部门的人员都记录在同一个单元格内。如何才能将单元格中的人员拆分到多行单元格中，对应的部门也要展示出来呢？

方法一：利用PQ拆分单元格内容到多行

首先，将鼠标放在任意有数据的单元格中，点击【数据】-【来自表格/区域】，勾选【表格包含标题】，然后确定。

选中【人员】这一列，然后点击【拆分列】-【按分隔符】来拆分，然后输入分隔符【、】，展开下方的【高级选项】，选择拆分到行，然后确定。

下面，这份名单就已经拆分出来了，我们再点击【关闭并上载至】，选择现有工作表，选择A8单元格即可。

效果如下：

温馨提示:PQ仅在office2016及以上版本适用，WPA版本也暂不支持。

方法二：用空格替换提取法来拆分单元格内容到多行

如果你的excel版本较低，无法使用PQ的话，建议大家用空格替换提取法来解决这个问题。该法是一个经典套路，用多个（如99个）空格来替换分隔符，然后完成字符数不等的数据提取。

步骤1：计算各部门人数

首先，在C2单元格输入公式=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1，然后下拉公式，获取各个部门的人数。

步骤2：获取部门名称

下面，再在D2单元格中输入公式=OFFSET($A$1,INT((ROW(A1)-1)/MAX($C$2:$C$6))+1,0)，然后下拉填充。

此公式的含义是获取部门名称，重复的次数为C列的最大值。

步骤3：将人员名单拆分到多行

我们再在E2单元格输入公式获取人员名单，输完公式后下拉填充。

=TRIM(MID(SUBSTITUTE(OFFSET($B$1,INT((ROW(A1)-1)/MAX($C$2:$C$5))+1,0),"、",REPT(" ",99)),MOD(ROW(A1)-1,MAX($C$2:$C$6))*99+1,99))

步骤4：删除多余的单元格

最后，将多余的单元格删掉即可得到我们想要的效果。

提示：

可以改变公式，只用三步完成操作。

步骤1相同。

步骤2在D2中输入公式并下拉填充：

=TRIM(MID(SUBSTITUTE(CONCAT(REPT($A$2:$A$6&"、",$C$2:$C$6)),"、",REPT(" ",99)),99*(ROW(A1)-1)+1,99))，

步骤3在E2中输入公式并下拉填充：

=TRIM(MID(SUBSTITUTE(CONCAT($B$2:$B$6&"、"),"、",REPT(" ",99)),99*(ROW(A1)-1)+1,99))

方法2适用于低版本的excel表格，但很明显，这些公式都太长，对于新手来说着实不太友好，不利于掌握，下面再来给大家介绍一种相对讨巧的函数公式。

方法三：利用新函数TEXTSPLIT和TEXTJOIN来拆分单元格内容到多行

步骤1：计算各部门的人数

如下图所示，在C2单元格输入公式=COUNTA(TEXTSPLIT(B2,,"、"))，然后下拉填充。

公式含义：先用TEXTSPLIT函数将B2单元格按照分隔符“、”拆分到列，然后用COUNTA函数来计数，即完成各部门的人数统计。

步骤2：提取部门名称

在部门这一列，我们输入公式=TEXTSPLIT(CONCAT(REPT(A2:A6&"、",C2:C6)),,"、",1)

公式含义：

首先，我们使用REPT函数，即根据指定次数重复文本，函数结构=(文本,次数)。

将各个部门按照各自的人数来重复，如行政部有3个人，即重复3次，中间用、连接。

再用CONCAT函数来将这一组多行多列的数据合并在一起，最后再用TEXTSPLIT函数将它们拆分到同一列中。

下面，在人员这一列，输入公式 =TEXTSPLIT(TEXTJOIN("、",,B2:B6),,"、")

公式含义：先用TEXTJOIN函数将数据用“、”合并在同一个单元格，然后再用TEXTSPLIT来拆分。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

学习交流请加微信hclhclsc进群领取资料。

如何提取品牌信息？LOOKUP函数有绝招！

没有Textjoin函数，如何解决提取数据的问题？

Excel教程：如何制作带有层次和透视感的图表？

八大查找函数公式，轻松搞定数据中的多条件查找

版权申明：

本文作者逍遥；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。