手機號碼的資料清理

廣告行銷的賴社群上討論到手機號碼資料非常混亂，可能是前方少掉 0 、加上國碼 +886、或者是使用空格或破折號符號間格等等方式。本文說明如何在 Excel 或 Google 試算表快速進行手機號碼的資料清理 (data cleaning)。

Photo by One Idea LLC on StockSnap

問題狀況

因為試算表軟體儲存格格式設定問題，導致手機第一碼 0 消失，詳見 Excel 或 Google 試算表的手機號碼補 0 的解決方法
手機號碼前加上國碼 +886、00886、00-886 等
手機號碼數字使用空格或破折號 - 符號間隔，例如 091-234-5678、091 234 5678、或者是符號間隔位置不一致等

解決方式

1. 如何使用 Microsoft Excel 進行手機號碼的資料清理

(1) 先檢查資料長度不是 10 碼數字的資料，例如計算 B2 資料長度

= LEN(TRIM(B2))

觀察到不是數字的符號有加號 +、減號 - 、與空格

(2) 新增輔助欄，使用 SUBSTITUTE 函數去除原始資料內不是數字的符號

= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, " ", ""), "+", ""), "-", "")

(3) 取右邊9位數字，再補上最左邊的零

= TEXT(RIGHT(D2, 9), "0000000000")

2. 如何使用 Google Sheets 進行手機號碼的資料清理

與使用 Microsoft Excel 進行手機號碼的資料清理的步驟類似

(1) 同樣先檢查資料長度不是 10 碼數字的資料，例如計算 B2 資料長度

= LEN(TRIM(B2))

(2) 去除原始資料內不是數字的符號

在去除原始資料內不是數字的符號的步驟，改用 Google Sheets 專有的 REGEXREPLACE 函數可以更簡潔地處理。

= REGEXREPLACE(B2&"", "[^\d]+", "")

第 1 個參數「系統會取代這段文字的部分區段」此處「B2&""」，將原始資料黏上空字串，是因為原始資料有可能都是數字，而沒有夾雜任何符號。使用 REGEXREPLACE 會造成 #VALUE! 錯誤： REGEXREPLACE 函式第 1 參數」需要文字值，但「1234567890」是數字，無法強迫成為文字。」，因此原始資料黏上空字串，強制將數字轉成文字。

第 2 個參數「規則運算式。系統將替換 text 中所有相符的項目」此處「 [^\d]+ 」指一個或多個不是數字的文字。

第 3 個參數「系統會將這段文字插入原來的文字」此處「""」會將符合第 2 個參數的文字直接刪除，達到去除原始資料內不是數字的目的。

(3) 取右邊9位數字，再補上最左邊的零

= TEXT(RIGHT(D2, 9), "0000000000")

講個秘訣：如果想將手機資料重新格式，例如從 0912345678 改成 0912-345-678，可以將上方公式改成：

= TEXT(RIGHT(D2, 9), "0000-000-000")

資料輸入建議

如果可以修改資料表單的話，在收集資料之前，限定手機號碼欄位是 10 碼數字，就可以節省事後資料清理的功夫。

手機號碼測試資料

+886912345678
+886 0912345678
+886-912-345-678

+886.912345678

886912345678

00886912345678
00-886912345678
+00886912345678
0886912345678
000886912345678
912345678
0912345678
0912-345678
0912-345-678
091-234-5678
0912 345678
0912 345 678
091 234 5678

Err

搜尋此網誌