有網友想要將在 Excel 中如下圖左的原始資料表(看起來像電話號碼)轉換為如下圖右的資料格式,該如何處理?

觀察下圖,看起來是要將『(』消掉,然後將『)』轉換為『-』,其餘不變。

Excel-轉換資料格式(SUBSTITUTE,VALUE,數值格式設定)

以下分為二個部分來設計公式。

1. 轉換為文字型態

儲存格C2:=SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","-")

SUBSTITUTE(A2,"(",""),"):將『(』置換為空白。

SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","-"):將上式結果中的『)』轉換為『-』。

其結果是一個「文字」型態。複製儲存格C2,貼至儲存格C2:C19。


2. 轉換為數字型態

儲存格C2:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-",""))

先利用三個 SUBSTISTUTE 函數,將『(、)、-』全部消除,再將這個文字結果使用 VALUE 函數轉換為數值格式。

接著設定儲存格C2的數值格式,自訂為:???-???-????

其結果是一個「數字」型態。複製儲存格C2,貼至儲存格C2:C19。

Excel-轉換資料格式(SUBSTITUTE,VALUE,數值格式設定)

arrow
arrow
    全站熱搜

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