學不完．教不停．用不盡

在 Excel 裡執行資料處理，常會需要將儲存格內容依分隔符號分成多欄或多列，或是相反程序處理。該如何設計？

1. 將儲存格內容依分隔符號分成多列

儲存格C4：

=FILTERXML("<X><A>"&SUBSTITUTE($A$4,"、","</A><A>")&"</A></X>","X/A["&ROW(A1)&"]")

複製儲存格C4，貼至儲存格C4:C27。

(1) 因為 FILTERXML 的語法為：FILTERXML(xml, xpath)，其中的語法結構是以<文字>開頭，</文字>結尾。

(2 利用 SUBSTITUTE 函數將「/」置換成「</A></X>」，再配合頭尾的<X>和</X>，組成完整的 XML 語法。

(3 "X/A" 為第一層：X，第二層：A。

(4) "X/A["&ROW(A1)&"]")：X/A[數字]，其中的數字n，表示第n筆資料。

(5) ROW(A1)會傳回「1」，向下複製公式時，ROW(A1)=1→ROW(A2)=2→ROW(A3)=3→….，即 n=1, 2, 3, …。

2. 將多列儲存格內容依分隔符號合成一個儲存格

儲存格C4：=TEXTJOIN("、",,A4:A27)

假設分隔符號：「、」，公式裡將儲存格A4:A27的內容合成一個字串，並以「、」串接。

3. 將儲存格內容依分隔符號分成多欄

儲存格C4：
=FILTERXML("<X><A>"&SUBSTITUTE($A$4,"、","</A><A>")&"</A></X>","X/A["&COLUMN(A1)&"]")

複製儲存格C4，貼至儲存格C4:Z4。

仿「1. 將儲存格內容依分隔符號分成多列」的公式，公式的差別在置換將 ROW(A) 置換為 COLUMN(A1)。

【參考資料】

 FILTERXML 函數參考微軟提供的說明網頁：FILTERXML 函數

 SUBSTITUTE 函數參考微軟提供的說明網頁：SUBSTITUTE 函數

 TEXTJOIN 函數參考微軟提供的說明網頁：TEXTJOIN 函數