參考下圖,在 Excel 裡有多個儲存格裡都有數列的資料,如何將這些儲存格內容轉換至多個儲存格?
如果你願意利用手動處理,可以使用「資料剖析」方式來操作,但是如果想要以公式自動化的處理,該如何設計公式?
【公式設計與解析】
因為原始資料內容,每個儲存格內有不同個數列的資料,所以處理起來公式很較長。很抱歉了!公式就麼長。
儲存格A4:
=SUBSTITUTE(MID(SUBSTITUTE(TEXTJOIN(CHAR(10),FALSE,$A$1:$A$3),
CHAR(10),REPT(" ",50)),(ROW(1:1)-1)*50+1,50)," ","")
複製儲存格A4,貼至儲存格A4:A13。
(1) TEXTJOIN(CHAR(10),FALSE,$A$1:$A$3)
利用 TEXTJOIN 函數將儲存格A1:A3的內容串接在一起,並且以CHAR(10)串接。
因為在儲存格裡輸多列資料要按 Alt+Enter 鍵,其會串接不可見字元 CHAR(10)。
(2) SUBSTITUTE(第(1)式,CHAR(10),REPT(" ",50))
將第(1)式傳回值裡的 CHAR(10) 以 50 個空格取代。
50 只是一個很大的數字,只要遠比儲存格裡的字串長度大即可。
(3) MID(第(2)式,ROW(1:1)-1)*50+1,50)
將第(2)式的傳回值,每間隔 50 個字取 50 個字。(其中會包含很多的空格)
ROW(1:1)-1)*50+1:公式向下複製時會產 1, 51, 101, 151, …。
(4) SUBSTITUTE(第(3)式," ","")
將第(3)式的傳回值裡的空格以空字串取代。
留言列表