編按：

如何根據(jù)分隔符將各單元格中的內(nèi)容批量拆分為多行？今天主要來教大家3種方法：PQ拆分法，空格替換提取法，OFFICE365函數(shù)法。

如下圖所示，這是關(guān)于各個部門派出的參會名單，每個部門的人員都記錄在同一個單元格內(nèi)。如何才能將單元格中的人員拆分到多行單元格中，對應(yīng)的部門也要展示出來呢？

方法一：利用PQ拆分單元格內(nèi)容到多行

首先，將鼠標(biāo)放在任意有數(shù)據(jù)的單元格中，點擊【數(shù)據(jù)】-【來自表格/區(qū)域】，勾選【表格包含標(biāo)題】，然后確定。

選中【人員】這一列，然后點擊【拆分列】-【按分隔符】來拆分，然后輸入分隔符【、】，展開下方的【高級選項】，選擇拆分到行，然后確定。

下面，這份名單就已經(jīng)拆分出來了，我們再點擊【關(guān)閉并上載至】，選擇現(xiàn)有工作表，選擇A8單元格即可。

效果如下：

溫馨提示:PQ僅在office2016及以上版本適用，WPS版本也暫不支持。

方法二：用空格替換提取法來拆分單元格內(nèi)容到多行

如果你的excel版本較低，無法使用PQ的話，建議大家用空格替換提取法來解決這個問題。該法是一個經(jīng)典套路，用多個（如99個）空格來替換分隔符，然后完成字符數(shù)不等的數(shù)據(jù)提取。

步驟1：計算各部門人數(shù)

首先，在C2單元格輸入公式=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1，然后下拉公式，獲取各個部門的人數(shù)。

步驟2：獲取部門名稱

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

此公式的含義是獲取部門名稱，重復(fù)的次數(shù)為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))

方法三：利用新函數(shù)TEXTSPLIT和TEXTJOIN來拆分單元格內(nèi)容到多行

步驟1：計算各部門的人數(shù)

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

公式含義：先用TEXTSPLIT函數(shù)將B2單元格按照分隔符“、”拆分到列，然后用COUNTA函數(shù)來計數(shù)，即完成各部門的人數(shù)統(tǒng)計。

步驟2：提取部門名稱

在部門這一列，我們輸入公式=TEXTSPLIT(CONCAT(REPT(A2:A6&"、",C2:C6)),,"、",1)

公式含義：

首先，我們使用REPT函數(shù)，即根據(jù)指定次數(shù)重復(fù)文本，函數(shù)結(jié)構(gòu)=(文本,次數(shù))。

將各個部門按照各自的人數(shù)來重復(fù)，如行政部有3個人，即重復(fù)3次，中間用、連接。

再用CONCAT函數(shù)來將這一組多行多列的數(shù)據(jù)合并在一起，最后再用TEXTSPLIT函數(shù)將它們拆分到同一列中。

下面，在人員這一列，輸入公式 =TEXTSPLIT(TEXTJOIN("、",,B2:B6),,"、")

公式含義：先用TEXTJOIN函數(shù)將數(shù)據(jù)用“、”合并在同一個單元格，然后再用TEXTSPLIT來拆分。

關(guān)于TEXTJOIN函數(shù)和TEXTSPLIT函數(shù)，在之前的教程中也會大家講解過，可以戳以下鏈接進(jìn)行查看。

一個既能分行又能分列的文本拆分函數(shù)TEXTSPLIT

用TEXTJOIN函數(shù)查找所有符合條件的值并填寫到一個單元格中

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

如何提取品牌信息？LOOKUP函數(shù)有絕招！

沒有Textjoin函數(shù)，如何解決提取數(shù)據(jù)的問題？

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者逍遙；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。