OmniDocExcel手机号脱敏和校验公式怎么写
整理Excel里手机号脱敏、格式校验和测试号码生成的常用公式，说明每个公式的写法、适用场景和边界，并给出导出前的核对方法，帮你批量处理一列手机号。
很多人手里都有一列客户手机号。发给同事看之前要遮住中间四位，导入系统前要筛出位数不对的记录，做测试数据时还要临时造一批号码。这三类工作都可以用 Excel 公式完成，不必逐个手动修改。
这篇文章讲三件事：怎么用公式给手机号打码，怎么判断一个号码的格式对不对，以及怎么生成测试用的号码。每类公式都会说明适用场景和它做不到的地方。读完后，你可以直接把公式套到自己的表格里。
手机号脱敏、校验还是生成，哪种公式适合你？一句话结论
先判断你要解决的是哪一类问题，再选公式。脱敏只改变显示效果，校验只给出判断结果，生成只用于测试，三者互不替代。
| 你的情况 | 推荐做法 | 原因 |
|---|---|---|
| 要把名单发给别人看，但不想露出完整号码 | 用 REPLACE 公式替换中间四位，结果粘贴为数值后另存 | 操作简单，结果直观，原始列可以不发出去 |
| 要在导入前找出格式有问题的号码 | 用 IF 加 AND 的校验公式，输出“格式正确”或“格式有误” | 能一次判断位数、数字和号段首位，便于筛选 |
| 要做系统测试，需要大量看起来像手机号的数据 | 用 RANDBETWEEN 生成，粘贴为数值后使用 | 随机结果不会重复规律，便于批量造数 |
| 要把号码替换成可对应回去的编号 | 用序号映射加一张单独保管的对照表 | 公式本身不能安全加密，对照表才是还原的依据 |
开始之前：先确认这几点
1. 手机号列是文本还是数字。 如果单元格显示为科学计数法，或者以 0 开头的号码前导 0 消失，说明它被当成了数字。公式结果会因此出错。建议先把整列设为文本格式，再重新粘贴号码。
2. 号码里有没有空格、横线或国家代码。 从网页或其他系统复制来的号码，常带有空格、“-”或“+86”。这些字符会让位数判断失效，所以要先清理，再做脱敏或校验。
3. 表格里是否有标题行和空行。 公式从第二行开始向下复制时，如果中间夹着标题或空行，结果会错位。动手前先看一眼数据范围。
4. 你要的是脱敏还是加密。 脱敏只是把数字变成星号，原文仍在表格里，任何能打开文件的人都能看到。真正需要保护的数据，要单独保管原始文件，并限制分享范围。
5. 号段规则会随时间调整。 目前中国大陆手机号以 1 开头，11 位，第二位在 3 到 9 之间较为常见，但具体号段分配以工业和信息化部的新规定为准。公式里的规则只适合做初步筛查，不要当作绝对标准。
用公式给手机号中间四位打码的步骤
- 在 Excel 中打开表格，确认手机号位于 A 列，第二行开始是数据。如果号码显示异常，先把整列设为文本格式，再重新输入或粘贴。
- 在空白列（例如 B 列）的 B2 单元格里，先清理空格和横线，公式写作：=SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",""),"-","")。这一步得到的是干净的号码。
- 在 C2 单元格输入脱敏公式：=REPLACE(B2,4,4,"\\\\")。它的意思是从第 4 位开始替换 4 个字符，前三位和后四位保留。
- 双击 C2 右下角的填充柄，或拖动到最后一行，让公式向下复制。
- 检查结果是否为“138\\\\0000”这样的格式。如果某一行出现错误值，说明该行号码不是 11 位，需要回到原数据核对。
- 选中 C 列全部结果，复制后用“选择性粘贴”中的“值”粘贴到新的一列。这样删除 B 列或修改原数据时，结果不会变成错误引用。
- 另存为新文件，只把脱敏后的结果发出去。原始明文表格单独保存在安全位置，不要和脱敏结果放在同一个文件里。
做完后逐项核对
脱敏结果生成后，不要直接发送。逐项检查下面几点，能避免大部分错误。
| 检查项 | 怎么看 | 有问题怎么办 |
|---|---|---|
| 位数是否统一 | 在空白单元格用 LEN 函数统计，或筛选出长度不等于 11 的行 | 回到原数据，删除空格和多余字符后重新计算 |
| 中间是否都被替换 | 随机抽查 5 到 10 行，看星号是否只出现在第 4 到 7 位 | 检查 REPLACE 的参数是否写成了起始位置 4、长度 4 |
| 是否残留明文 | 用查找功能搜索原始号码的前三位加后四位 | 确认结果列已粘贴为数值，并删除引用原列的公式 |
| 是否有错误值 | 筛选出以#号开头的单元格 | 找到对应行，判断是空单元格还是号码位数不对 |
| 文件是否混入原始列 | 打开另存的文件，检查隐藏列和工作表 | 删除隐藏列，或重新只复制结果列生成新文件 |
📊 核心对比
手机号处理通常有四种需求，对应的公式思路不同。下表列出每种公式的写法和注意事项。
| 需求 | 公式写法（以 A2 为例） | 注意事项 |
|---|---|---|
| 脱敏展示 | =REPLACE(A2,4,4,"\\\\") | 只改变显示，原值仍可见，不是加密 |
| 格式校验 | =IF(AND(LEN(A2)=11,ISNUMBER(--A2),LEFT(A2,1)="1",MID(A2,2,1)>="3"),"格式正确","格式有误") | 只检查位数、数字和号段首位，不能确认号码是否真实在用 |
| 测试号码生成 | ="1"&RANDBETWEEN(3,9)&TEXT(RANDBETWEEN(0,999999999),"000000000") | 结果会随表格刷新而变化，粘贴为数值后固定；只用于测试 |
| 替代加密 | ="U"&TEXT(ROW()-1,"0000") | 编号需要配合单独保存的对照表才能对应回原号码，公式本身无法还原 |
使用校验公式时，有两点要注意。一是 ISNUMBER(--A2)用来判断内容能否转为数字，如果号码中混有字母，结果会显示“格式有误”。二是 MID(A2,2,1)>="3"是按文本比较第二位字符，适合单个数字，不适用于多位比较。
生成测试号码时，也要注意用途。随机生成的号码可能与真实号码相同，不能用于注册账号、发送短信或冒充他人。在测试系统中使用时，建议在文件中标明“测试数据”。
❓ 常见问题（FAQ）
1. 手机号脱敏和加密有什么区别？
脱敏是把部分字符显示为星号，原始数据仍在表格里，只要能打开文件就能看到完整号码。加密需要用专门的工具和密钥，把内容转换成无法直接读取的形式。Excel 公式只能做脱敏和编号替换，不能替代加密。
2. 为什么输入手机号后显示成科学计数法？
Excel 把 11 位数字当作数值处理，单元格宽度不够时会自动显示成科学计数法。先把单元格设为文本格式，再输入号码，问题通常可以解决。如果号码已经变形，需要从原始来源重新导入。
3. 校验公式能判断号码是否真的存在吗？
不能。公式只能检查格式，比如位数、数字和号段首位。号码是否真实在用、是否属于某个运营商，需要通过业务系统或正规的验证渠道确认。
4. 号码前面有“+86”或空格，公式会出错吗？
会。带“+86”的号码超过 11 位，校验公式会判断为格式有误。处理前先用 SUBSTITUTE 去掉“+86”和空格，再进行校验。如果数据量大，可以先用查找替换功能统一清理。
📝 总结
手机号脱敏、校验和生成，分别需要 REPLACE、IF 加 AND 和 RANDBETWEEN 这类公式。先清理数据，再套用公式，最后逐项核对结果，才能确保表格可用。脱敏结果要粘贴为数值后另存，原始明文单独保管，这样才能在处理数据的同时保护个人信息。
