學不完．教不停．用不盡

在 Excel 中每個儲存格內容由多列的文字組成，如何能分別取出每一列的文字？

（參考下圖）

【公式設計與解析】

儲存格C2：=SUBSTITUTE(MID(SUBSTITUTE($A2,CHAR(10),REPT(" ",50)),
(COLUMN(A:A)-1)*50+1,50)," ","")

複製儲存格C2，貼至儲存格C2:F5。

設計思維參考如下的步驟圖：

(1)將CHAR(10)以50個空格取代

(2)依指定位置取出50個字元

(3)將多餘的空格刪除

儲存格中的多列文字是使用 Alt+Enter 鍵換列，會插入一個不可視字元：CHAR(10)。

(1) REPT(" ",50))

利用 REPT 函數產生 50 個空格。

(2) SUBSTITUTE($A2,CHAR(10),REPT(" ",50))

利用 SUBSTITUTE 函數，將儲存格A2內容中的 CHAR(10)(換列字元)，以 50 個空格取代。

(3) MID(第(2)式,(COLUMN(A:A)-1)*50+1,50)

COLUMN(A:A)：會傳回儲存格的欄號。當公式向右複製時會產生COLUMN(A:A)=1、COLUMN(B:B)=2、COLUMN(B:B)=3、…。

(COLUMN(A:A)-1)*50+1：當公式向右複製時會產生1, 51, 101, ...。

再利用 MID 函數分別取出儲存格A2中，第1個位置起的50個字元、第51個位置起的50個字元、第101個位置起的50個字元、...。

(4) SUBSTITUTE(MID(第(2)式,第(3)式," ","")

再次利用 SUBSTITUTE 函數將第(3)式的傳回結果中的空格以空字串取代（相當於刪除空格）。

【延伸應用】

若是內容改成一列，而資料是由「,」隔開，則公式修改：

儲存格C2：=SUBSTITUTE(MID(SUBSTITUTE($A2,",",REPT(" ",50)),
(COLUMN(A:A)-1)*50+1,50)," ","")

即將「CHAR(10)」置換成「","」即可。

【延伸閱讀】

參考：本部落格中其他關於 Excel SUBSTITUTE 函數的應用