Excel手机号处理:防错录入、删除重复、提取、脱敏(保密)、分段显示,一次理清
- 2026-11-06 18:58:36
做名单、发通知、公布获奖信息时，手机号反复处理，核心其实只有四步：
防错录入 → 清洗整理 → 提取、脱敏 → 分段显示
下面按这个顺序来，公式可直接复制。
一、录入时就把错号拦住：数据验证
等到事后发现号码不是11位、不是以 1 开头，再回头核对成本很高。录入前用数据验证拦住最省事。
选中手机号列 → 数据 → 数据验证 → 允许“自定义”，公式输入：
=AND(LEN(C2)=11,ISNUMBER(C2),LEFT(C2,1)="1")
含义：长度必须是11位，必须是数值，且以 1 开头。
设置好后，输入错误格式的号码会直接弹出警告，从源头上减少清洗工作量。
二、先洗数据：文本转数值 + 去重
1. 文本格式的手机号，分段格式不生效
从系统导出、从聊天记录复制过来的手机号，经常带绿色小三角，本质是文本。文本不参与数值自定义格式，所以 000 0000 0000不会生效。
批量转数值，最快的是分列：
选中整列 → 数据 → 分列 → 直接点“完成”。整列一次性转为数值，不用辅助列。
也可以直接设置公式：=--H3
2. 手机号去重
名单合并后重复号码很常见。
可以用数据 → 删除重复值，勾选“姓名”“手机号”列。
注意：去重前先确认文本格式已统一，文本 "13812345678" 和数值 13812345678 在部分去重逻辑中可能被视为不同值。
三、从杂乱文本中提取手机号：按软件选公式
场景1：WPS 用户，首选 REGEXP
提取第一个手机号
输入公式=REGEXP(A2,"1[3-9]\d{9}")
提取一行中的全部手机号
输入公式=TEXTJOIN("、",TRUE,REGEXP(A2,"1[3-9]\d{9}",3))
仅 WPS 支持，Excel 原生不识别，粘贴会报 #NAME?。
场景2：Excel /WPS用户，只取第一个号码
输入公式=MID(A2,MIN(FIND(ROW($1:$10)-1,A2&5^19)),11)
原理：ROW($1:$10)-1 生成 0–9，FIND 定位每个数字第一次出现的位置，MIN 取最早出现的位置，MID 从那里截11位。A2&5^19 是为了防止找不到数字时报错。
旧版 Excel 需要按 Ctrl + Shift + Enter 三键结束；新版 Excel 和 WPS 直接回车即可。
场景3：Excel /WPS用户，取最后一个号码
输入公式=LOOKUP(9E+307,--MID(A2&"a",ROW($1:$99),11))
LOOKUP(9E+307,...) 取最后一个有效数值。如果一行有多个手机号，它返回靠后的那个，不是第一个。
场景4：电话号码就在文本最右边
如果手机号固定出现在末尾，直接输入公式：=RIGHT(A2,11)
这类规整场景不需要数组公式。
一句话选择： WPS 用 REGEXP；Excel 取第一个用 MID+FIND；Excel 取最后一个用 LOOKUP；号码在首尾直接用 LEFT / RIGHT。
四、隐私脱敏：隐藏中间四位
公布名单、快递信息、回访记录，最常用就是这一条：
输入公式：=REPLACE(B2,4,4,"****")
效果：13812345678 → 138****5678。
REPLACE 生成的是新内容，不会破坏原号码。要彻底脱敏，复制结果后 粘贴为值。
五、分段显示：只改外观，不改数值
操作步骤
选中手机号所在单元格或整列；
按
Ctrl + 1打开“设置单元格格式”；选择“数字”→“自定义”；
根据以下目标输入对应内容：
空格分段：000 0000 0000
横线分段：000-0000-0000
效果展示：
前提：单元格必须是数值格式。文本格式的自定义格式不会按数字占位符显示。
六、一个最容易踩的坑：分段和脱敏不要混在同一列做
分段显示是自定义格式，脱敏是公式生成的新内容。如果先 REPLACE 脱敏，再对结果设置 000 0000 0000，因为中间已经是 ****，格式不会按预期恢复成分段数字。
正确顺序：
要对外公布脱敏号码：先脱敏，显示
138****5678即可，不需要再套分段格式；要内部查看分段号码：只对原始号码列设置自定义格式，不要破坏原值。
注：以上姓名及手机号信息均为虚构。
