我們在Excel表格中批量錄入手機號，就希望錄入的手機號是正確的。判斷手機號碼是否正確，要看以下兩點：
- 第一、手機號長度為11位。
- 第二、手機號有一定的號碼段，長度11位的數字不一定是手機號，例如129開頭的數字就肯定不是手機號。
另外，你還可能需要知道手機號屬於移動、聯通還是電信。
今天，我將用Excel解決這些問題。
判斷手機號長度是否正確
這個非常簡單，我們知道可以用“LEN(單元格)”函數獲得指定單元格的字符長度，所以只要輸入以下公式，就可以知道手機號的長度是否合適啦。
=IF(LEN(B2)=11,"正確","錯誤")
判斷手機號號碼段是否正確
由前文我們知道了有效手機號碼段，所以只要判斷手機前3位或前4位數字是否在號碼段中，就知道這個手機號碼是否正確啦。
為了讓公式讓簡單一些，我整理了所有正確的4位號碼段，如下圖所示。
這樣，用下面這個公式判斷手機號碼，就更準確啦。
=IF(IFERROR(MATCH(--LEFT(B2,4),手機號碼段!C:C,0),0),"","錯誤")
這個公式有點長，怎麼理解呢？
① --LEFT(B2,4)用於提取號碼前4位數字。
為什麼要在LFET函數前加個“--”呢。看上去，加不加“--”的結果都一樣啊。
這就和MATCH函數相關啦。
②用MATCH(查找值,去哪找,精確查找嗎）在手機號碼段表中查找號碼段，找到後返回號碼段位置，找不到就返回“#N/A”。
由下圖可知，只有在LEFT函數前添加了“--”，才能得到正確結果。這又是為什麼呢？
其實，這是格式在作怪，手機號碼段中的數字是數值格式，而用LEFT函數直接提取到的是文本格式，二者格式不一，用MATCH函數自然得不到正確的結果。只有添加“--”將文本格式轉換為數值格式。
③ MATCH函數查找不到的單元格會得到“#N/A”，所以用IFERROR公式過濾，最後再用IF公式判斷正誤。
判斷手機號屬於哪個運營商
有了前面的基礎，判斷手機號的歸屬運營商就容易啦。MATCH獲得號碼段位置後，用INDEX函數獲取同一行D列的運營商名稱。這樣，公式就變成了：
=IFERROR(INDEX(手機號碼段!D:D,MATCH(--LEFT(B2,4),手機號碼段!C:C,0)),"號碼錯誤")
最終公式
結合判斷長度和運營商公式，最後可以得到下面這個公式。
=IF(LEN(B2)=11,IFERROR(INDEX(手機號碼段!D:D,MATCH(--LEFT(B2,4),手機號碼段!C:C,0)),"號碼錯誤"),"號碼長度錯誤")
雖然公式長了點，但它既能判斷號碼是否正確，又能判斷號碼歸屬運營商。
如果你不喜歡很長的公式，可以按照我的思路，分步求得需要的數據，最後用一個簡單的公式獲得結果。
閱讀更多 解晴新生 的文章
