參考下圖,在 Excel 裡有多個儲存格裡都有數列的資料,如何將這些儲存格內容轉換至多個儲存格?

如果你願意利用手動處理,可以使用「資料剖析」方式來操作,但是如果想要以公式自動化的處理,該如何設計公式?

Excel-將多個多列儲存格內容分成多列儲存格(SUBSTITUTE,CHAR,TEXTJOIN,MID,REPT)

 

【公式設計與解析】

因為原始資料內容,每個儲存格內有不同個數列的資料,所以處理起來公式很較長。很抱歉了!公式就麼長。

儲存格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)。

Excel-將多個多列儲存格內容分成多列儲存格(SUBSTITUTE,CHAR,TEXTJOIN,MID,REPT)

(2) SUBSTITUTE(第(1)式,CHAR(10),REPT(" ",50))

將第(1)式傳回值裡的 CHAR(10) 以 50 個空格取代。

50 只是一個很大的數字,只要遠比儲存格裡的字串長度大即可。

Excel-將多個多列儲存格內容分成多列儲存格(SUBSTITUTE,CHAR,TEXTJOIN,MID,REPT)

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

將第(2)式的傳回值,每間隔 50 個字取 50 個字。(其中會包含很多的空格)

ROW(1:1)-1)*50+1:公式向下複製時會產 1, 51, 101, 151, …。

Excel-將多個多列儲存格內容分成多列儲存格(SUBSTITUTE,CHAR,TEXTJOIN,MID,REPT)

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

將第(3)式的傳回值裡的空格以空字串取代。

學不完.教不停.用不盡文章列表

arrow
arrow
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(1) 人氣()