我们在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)),"号码错误"),"号码长度错误")
虽然公式长了点，但它既能判断号码是否正确，又能判断号码归属运营商 。
如果你不喜欢很长的公式，可以按照我的思路，分步求得需要的数据，最后用一个简单的公式获得结果 。
本文由解晴新生原创，欢迎点赞、评论、关注和点击头像 。