2016年4月21日 星期四

Excel-修正電話號碼分機符號(SUBSTITUTE,REPLACE)

有網友用 Excel 處理關於電話號碼資料時,發現每個人使用的分機符號都不相同,如何才能統一呢?
參考下圖,在電話清單中表示分機的符號有『*、-、+、#』,想要統一為『#』,該如何處理?而電話號碼有可能是 7 碼,也可能是 8 碼。
Excel-修正電話號碼分機符號(SUBSTITUTE,REPLACE)

【公式設計與解析】
(1) 電話號碼長度一致
儲存格C2:=REPLACE(A2,12,1,"#")
當電話話碼長度都一致時(例如:全為8碼、全為7碼),使用 REPLACE 函數指定第12個字元開始的 1 個字元,全都置換為『#』。

參考:REPLACE 函數
REPLACE 會根據您指定的字元數,以不同的文字字串來取代文字字串的某一部分。
語法:REPLACE(old_text, start_num, num_chars, new_text)
Old_text(必要):這是要取代其中某些字元的文字。
Start_num(必要):這是在 old_text 中要以 new_text 取代的字元位置。
Num_chars(必要):這是在 old_text 中要 REPLACE 以 new_text 取代的字元數。
New_text(必要):這是在 old_text 中要取代字元的文字。

(2) 電話號碼長度不致
儲存格C2:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"*","#"),"+","#"),"-","#",2)
當電話話碼長度可能不一致時(例如:部分為8碼、部分為7碼),可能造成要被置換的字元位置不相同,所以不能使用 REPLACE 函數,因此改用 SUBSTITUTE 函數。使用 SUBSTITUTE 函數可以將字串中的某些文字置換成不同文字。
比較特殊的是要將『-』置換成『#』時,必須置換第 2 個『-』,請注意公式中參數的使用。

參考:SUBSTITUTE 函數
將文字字串中的 old_text 部分以 new_text 取代。 要取代文字字串中的特定字串時,可以使用 SUBSTITUTE;若要取代文字字串中特定位置上的任何字串,請使用 REPLACE。
語法:SUBSTITUTE(text, old_text, new_text, [instance_num])
Text(必要):這是包含要以字元取代文字的文字或參照。
Old_text(必要):這是要取代的文字。
New_text(必要):這是要用來取代 old_text 的文字。
Instance_num(選用):指定要將第幾個 old_text 取代為 new_text。
Excel-修正電話號碼分機符號(SUBSTITUTE,REPLACE)

沒有留言:

張貼留言

檢視其他文章

好康東東