让 Excel 先帮忙粗筛数据，完全不符合规则的号码自动高亮区分

后台收到读者提问：由于工作需要，需要定期收集手机号，但是人工填写的手机号码很不规范，有很多无效号码。

能否制定一些最基本的规则，可以先初步筛选出不规范的号码？比如，国内手机都是以“1”开头，长度为 11 位。如果连这两个要求都不符合，那肯定不是有效手机号。

案例：

下图 1 是收集上来的手机号码表，请按粗放的规则初步区分出有效和无效号码，规则如下：

• 手机号码必须以“1”开头；

• 长度必须为 11 位

效果如下图 2、3 所示。

01

突出正确的

1. 选中 B 列 --> 选择菜单栏的“开始”-->“条件格式”-->“突出显示单元格规则”-->“介于”

2. 在弹出的对话框中将最小和最大值分别设置为“10000000000”和“19999999999”--> 点击“确定”：

所有符合规则的单元格都高亮显示了。

02

突出错误的

1. 选中 B 列的数据区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

2. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”按钮：

=OR(LEN(B2)<>11,LEFT(B2,1)<>'1')

公式释义：

• LEN(B2)<>11：B2 的字符数位数不等于 11；

• LEFT(B2,1)<>'1'：B2 的第一位不是“1”

• OR(...,...)：上述两个条件只要满足其一就触发条件

* 公式中的参数都要相对引用。

3. 在弹出的对话框中选择“填充”选项卡 --> 选择所需的填充色 --> 点击“确定”

4. 点击“确定”

现在高亮显示的是不符合条件的单元格。

03

只保留正确的

1. 选中数据表的任意单元格 --> 选择菜单栏的“数据”-->“从表格”

2. 在弹出的对话框中保留默认设置 --> 点击“确定”

表格已经上传至 Power Query。

3. 点击“手机”旁边的筛选按钮 --> 在弹出的菜单中点击“数字筛选器”旁边的小箭头 --> 选择“介于...”

4. 在弹出的对话框中，在最小和最大值处分别输入“10000000000”和“19999999999”--> 点击“确定”

5. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至”

6. 在弹出的对话框中选择“现有工作表”及所需上传至的位置 --> 点击“加载”

绿色的表格就是所有符合条件的数据。

如果能从源头就避免输入不规范的数据，那就更好了。具体操作方法可参阅 Excel – 如何强制规范手机号码输入规则？

转发、在看也是爱！