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

(參考下圖)

Excel-擷取儲存格內容分列中的每一列(SUBSTITUE,REPT,MID)

 

【公式設計與解析】

儲存格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)將多餘的空格刪除

Excel-擷取儲存格內容分列中的每一列(SUBSTITUE,REPT,MID)

儲存格中的多列文字是使用 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-擷取儲存格內容分列中的每一列(SUBSTITUE,REPT,MID)

【延伸閱讀】

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

 

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

arrow
arrow
    文章標籤
    Excel
    全站熱搜

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