1. 先弄清楚隐藏中间四位到底在隐藏什么做数据处理这行时间长了你会发现真正高频的需求不是那些炫技的函数嵌套而是把手机号中间四位打码这种听起来一句话就能说完的小事。我这几年帮人处理过客户名单、学员报名表、员工花名册、供应商联系人几乎每一份表最后都要过一遍这道工序把手机号、身份证号这类可以直接定位到具体个人的信息在对外流转时遮掉一部分。原因很简单原始号码只要发出去一次就再也收不回来了而表格又偏偏是最容易随手转发的东西。所以这一篇我想把这件事彻底讲透。它看着简单实际上藏着三类完全不同的实现路径——公式法、显示格式法、批量处理法——这三条路各有各的适用场景也各有各的坑尤其是格式法和公式法的区别我见过太多人栽在这里自己在屏幕上明明看到的是 138****5678把文件发给对方对方一打开全是完整号码因为那只是看起来被隐藏了。这个误解造成的事故比函数写错带来的后果严重得多。这篇文章面向的人群很宽如果你是刚上手 Excel 的行政、人事、财务能直接抄走几条公式如果你是要定期输出报表的数据岗会看到 Power Query 的批量方案如果你要给同事写个小工具VBA 和正则的写法也在里面。另外我会把身份证输进去变成科学计数法Excel 复制粘贴突然失灵这些一做脱敏就必然撞上的问题一并说清楚因为它们不是独立问题而是同一条工作流上的拦路虎。1.1 三种脱敏目标保留位数完全不一样在动手写公式之前先确定你到底要遮几位。不同信息的敏感程度不同保留位数也不同遮多了对方核对不上遮少了等于没遮。我按常见业务场景整理了一张对照表你可以直接照着选数据类型总长度常见保留规则脱敏后示例适用场景手机号11 位保留前 3 后 4138****5678客户名单、外呼脚本手机号严格11 位保留前 3 后 2138******78大范围公开分发身份证18 位保留前 6 后 4110101********1234内部流转、核对身份身份证轻度18 位保留前 6 后 8110101****12345678需要留足核对信息身份证重度18 位只留前 1 后 11****************4对外公示银行卡号16-19 位保留后 4**** **** **** 1234财务对账邮箱不定保留首字符与域名z****abc.com通知类邮件看这张表你会发现手机号是保留前 3 后 4而身份证是保留前 6 后 4这两个数字不是随便定的。手机号前 3 位是号段能判断运营商后 4 位足够本人确认这是我的号身份证前 6 位是行政区划代码后 4 位包含顺序码和校验码中间那 8 位出生日期才是真正的敏感信息所以遮的正好是 8 位。银行卡保留后 4 位是因为很多人对自己卡号的后四位有记忆而对前几位没有。注意脱敏方案一旦定下来整个团队最好统一。我遇到过同一份名单里一半人手机号写成 138****5678另一半写成 138*******78最后汇总时对不上白白返工。1.2 先备份再动手这条规矩不要破我给自己定的铁律是任何脱敏操作都不在原数据列上直接做。先复制一份原表另存为xxx_源数据_勿动.xlsx或者至少在原始列右侧新增一列专门放脱敏结果。原因是脱敏本身是不可逆的**** 一旦覆盖掉原始号码除非你有备份否则没有任何函数能把它还原回来。用辅助列还有一个好处脱敏列和原始列可以并存一段时间交接前你再决定要不要把原始列删掉。很多公司对外交付时要求只保留脱敏列但内部核对阶段还需要原始列对照用辅助列就能同时满足两个阶段。等确认无误后把脱敏列复制一份、用选择性粘贴—值固化再隐藏或删除原始列这个流程我用了很多年几乎没出过问题。2. 公式派四条公式搞定九成场景公式法的核心优势是结果是真的文本不依赖任何单元格格式复制到任何地方都是打码后的样子不会出现发出去就露馅的情况。缺点是原始数据变了要重新算不过对于一次性处理的表格来说根本不算问题。下面这几条我从简单到复杂排了序你按数据干净程度挑着用。2.1 REPLACE 函数最直白、最不容易写错的一条REPLACE的语法是REPLACE(原文本, 从第几位开始, 替换几位, 换成什么)。手机号脱敏写成这样REPLACE(A2,4,4,****)读出来就是从第 4 位开始把 4 个字符替换成 4 个星号。11 位手机号变成前 3 位 星号 后 4 位正好 138****5678。这条公式我推荐给所有人的第一个理由就是参数自解释4,4两个数字对应从第 4 位遮 4 位改位数的时候不容易搞混。身份证同理遮中间 8 位REPLACE(B2,7,8,********)从第 7 位开始替换 8 位剩下的就是前 6 位行政区划加后 4 位。如果你们内部只需要遮出生日期那一小段写成REPLACE(B2,7,4,****)出来就是 110101****19900101 这种效果后面 8 位完整保留。星号的数量要和替换位数一致不然总长度会变虽然不影响阅读但有些系统的字段校验会因此报错。提示REPLACE和查找替换里的REPLACEB不是一回事后者按字节算长度用来处理中文混排时才需要日常脱敏别用错。2.2 LEFT RIGHT 拼接数据长度整齐时最顺手如果你更习惯截前截后拼起来的写法LEFT加RIGHT会更符合直觉LEFT(A2,3)****RIGHT(A2,4)意思就是左边取 3 位右边取 4 位中间用星号连起来。是文本连接符可以理解成用胶水把三段粘在一起。这条公式的好处是不需要你去数从第几位开始只要知道前面留几位、后面留几位就行。身份证写成LEFT(B2,6)********RIGHT(B2,4)LEFT和RIGHT的第二个参数是取几个字符写了超过实际长度的数字也不会报错只会把整串都取出来这一点在设计兜底逻辑时很有用。比如数据里混着 11 位的手机号和 8 位的固话你可以用IF(LEN(A2)11, 脱敏公式, A2)先判断长度再决定要不要处理。2.3 MID 兜底长度不齐、带分隔符的脏数据靠它真实表格里最不缺的就是脏数据。手机号写成138-1234-5678、86 13812345678、138 1234 5678的我都见过。这类数据前面加了东西用LEFT就不准了这时候MID更灵活LEFT(A2,3)****MID(A2,8,4)MID(文本, 起始位置, 长度)的意思是从第 8 个字符开始往后取 4 个。对于标准 11 位手机号第 8 到第 11 位就是后四位结果和RIGHT(A2,4)完全一样。它的价值在于当你需要取的不是最后几位而是某个固定区间时MID是唯一选择。处理带分隔符的号码我的做法是先用SUBSTITUTE把符号清掉再脱敏LET(t,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,-,), ,),86,),LEFT(t,3)****RIGHT(t,4))LET是较新版本才有的函数如果你的 Excel 打开报#NAME?说明版本不支持改成嵌套写法或者分两步做即可。清除分隔符这一步不能省否则你按 11 位长度算出来的位置全是错的。2.4 公式写对了结果为什么还是错的这是我踩过最深的坑也是必须单独拿出来说的一点公式对 18 位身份证不生效甚至越算越离谱。原因在于 Excel 的数值精度只有 15 位有效数字一个 18 位身份证如果被存成了数字格式从第 16 位开始就已经不是你想的那个数字了——它会被自动补成 0同时显示成1.10105E17这样的科学计数法。解决顺序是这样的先把身份证列选中设置单元格格式为文本然后重新输入或重新导入数据。注意这个顺序不能反已经变成科学计数法的单元格改成文本格式也只是把1.10105E17当字符串存起来丢失的数字回不来了。如果数据是从 CSV 导入的在导入向导里那一步就要把该列指定为文本别等到进了表格再改。手机号相对安全因为 11 位在 15 位精度范围内即使存成数值REPLACE也能正确读出完整的 11 位。但你要清楚计算结果是文本如果你想拿它去和另一张表做VLOOKUP被匹配的那一列也必须同样是文本否则匹配不上。这也是为什么我建议在脱敏时额外保留一个序号或工号列作为关联键别指望用脱敏后的号码做关联。3. 不写公式的两条路自定义格式与快速填充如果你只是想让屏幕上看起来清爽一点或者数据量小、只想快速糊弄过去前面两条路之外还有更省事的办法。但省事往往意味着有代价这一节我把两者的边界说清楚。3.1 自定义单元格格式一秒钟让号码变脸选中手机号列右键设置单元格格式—自定义在类型框里输入000****0000确定之后你会看到单元格显示成 138****5678而编辑栏里依然是完整的 13812345678。这里有个绝大多数人不知道的细节中间的星号必须用英文双引号包起来。因为自定义格式代码里*是一个特殊符号表示用后面的字符重复填充如果你直接写000****0000Excel 会把它理解成用 0 填满剩余空间结果是满屏的零而不是星号。这个格式针对的是数值型的 11 位手机号如果是文本型号码数字格式对它完全无效。另外如果你只是想整体不显示内容格式代码写;;;三段分号就够了单元格内容会被彻底隐藏但值还在取消格式就能恢复。注意自定义格式改的是显示方式不是内容本身。你把这一列复制到微信或者另一个文件里粘贴出来的仍然是完整号码。它只适合自己在屏幕上看、或者打印时遮挡绝不适合对外发送文件。这一条我重复三遍都不嫌多。3.2 CtrlE 快速填充爽但一定要验收2013 版之后的 Excel 有个快速填充操作方式是在目标列手动写好第一个示例比如 138****5678然后按CtrlEExcel 会猜出你的规律并自动填满整列。Mac 版 Excel 也支持这个功能如果快捷键没反应去数据选项卡里找快速填充按钮不同版本的菜单位置略有差异。它快是真的快但翻车也是真的翻车。快速填充本质上是模式识别不是规则计算遇到下面几种情况就会出错数据中间有空行它会在空行处断掉后面的不再填号码长度不一致它可能把短号也按 11 位切表里混了固话和手机号它分不清一律按第一个示例的规则处理。我的用法是CtrlE 填完之后立刻随机抽查 10 行用LEN()函数核对脱敏结果的长度是否都等于 11再扫一眼有没有哪一行的星号位置跑偏了。抽查这 10 行花不了一分钟但能挡掉大部分返工。如果数据量超过几千行或者格式特别乱我建议直接放弃快速填充去用下一节的批量方案。3.3 格式隐藏和公式隐藏本质区别在哪把这两条路摆在一起对比结论就很清楚了对比项自定义格式公式生成快速填充真实内容完整号码已打码文本已打码文本复制到其他文件仍是完整号码仍是打码文本仍是打码文本是否影响排序筛选不影响按真实值排按打码值排按打码值排电脑给别人看有泄露风险安全安全数据更新后自动跟随自动重算不跟随需重做适用数据量任意任意建议 3000 行以内版本要求无无Excel 2013我的判断标准很简单只要这份文件有可能离开你的电脑一律用公式绝不用格式。格式方案留给我自己的草稿表用来在核对时快速扫一眼那个场景下它确实好用。4. 大批量场景Power Query 与 VBA 怎么选当数据从几百行涨到几万行而且每个月都要重来一遍前面那些手工公式就开始拖后腿了。这时候有两个方向Power Query 走配置一次、每月刷新的路线VBA 走选中就跑、就地处理的路线。两条路我都用选择依据是任务的重复频率。4.1 Power Query每月都要做的报表一次配置终身受用Power Query 在 Excel 的数据选项卡下获取数据—来自表格/区域。把数据加载进去之后选择手机号列用添加列—自定义列写 M 语言公式 Text.Start([手机号], 3) **** Text.End([手机号], 4)注意 M 语言区分大小写Text.Start和Text.End的首字母都要大写列名要用方括号包起来。写完确定新列立刻生成。之后每次原始数据更新你只要点全部刷新脱敏列会自动重算完全不用再动公式。Power Query 真正的价值在于处理链路可以固化。你可以把清除分隔符 → 长度判断 → 脱敏 → 删除原始列 → 导出整条流水线搭好下次拿到结构相同的新数据换个文件路径刷新一遍就行。我有个做招聘的朋友每周要处理一批简历表搭好这套流程之后原本半小时的活压缩到了三分钟。有一点要提醒Power Query 里的空值处理需要显式判断否则遇到空单元格会报错。稳妥的写法是 if [手机号] null then null else Text.Start([手机号], 3) **** Text.End([手机号], 4)这一句加进去整列刷新时就不会因为某个空单元格中断。4.2 VBA几万行就地批量处理选中就干如果你的需求是在现有表格上直接把号码改成打码后的文本VBA 是最快的。按AltF11打开编辑器插入一个模块把下面这段贴进去Sub MaskPhone() Dim rng As Range, c As Range, s As String Application.ScreenUpdating False Application.Calculation xlCalculationManual Set rng Selection For Each c In rng s Trim(CStr(c.Value)) If Len(s) 11 Then c.Value Left(s, 3) **** Right(s, 4) ElseIf Len(s) 18 Then c.Value Left(s, 6) ******** Right(s, 4) End If Next c Application.Calculation xlCalculationAutomatic Application.ScreenUpdating True MsgBox 处理完成 End Sub用法是先在表里选中要处理的区域只选号码那一列别整表全选然后运行这个宏。它会自动按长度判断是手机号还是身份证分别用不同的规则处理。代码里有三个细节值得说。第一Application.ScreenUpdating False关掉屏幕刷新几万行循环时速度能快好几倍否则你会看到屏幕疯狂闪烁。第二Application.Calculation xlCalculationManual把自动重算改成手动避免每改一个单元格就触发全表重算处理完记得改回来。第三CStr先把值转成字符串再处理防止数值型的手机号在拼接时报类型错误。这个宏有个必须提前做的准备先把目标列设置成文本格式并做好备份。因为它直接覆盖原值跑完之后原始号码就没了。我一般是先把原始列复制到右侧隐藏起来再在左侧这列上跑宏。4.3 参数计算和性能实测心得关于性能我做过一次不太严谨但足够说明问题的测试4 万行手机号在关闭屏幕刷新和自动重算的 VBA 下跑完大约 3 到 6 秒不关的话要 20 秒以上而且中途会明显卡顿。数据超过 10 万行时Excel 本身的响应就会变慢这种情况下我建议直接上 Power Query 或者把数据丢到数据库里处理不要硬扛。还有一个容易被忽略的参数问题Left(s, 3)里的数字 3和Right(s, 4)里的 4这两个数字要和 1.1 节的对照表保持一致。如果你公司的规范是保留前 3 后 2那就改成Left(s,3) ****** Right(s,2)星号数量同步调整保证脱敏后总长度仍然是 11 位。长度不变这一点很重要因为有些下游系统会对字段长度做校验长度变了会直接导入失败。5. 一次完整的实操复盘从原始表到可交付文件说了这么多方法我把整个流程串一遍。假设手里是一份 2000 行的客户联系表需要发给合作方做电话回访要求手机号脱敏身份证号只保留前 6 后 4。5.1 操作流水第一步备份。原文件另存一份命名加上日期放进单独的文件夹。这一步三十秒能救你一天。第二步检查数据格式。选中手机号列和身份证列看编辑栏里显示的是文本还是数值。如果身份证列显示成科学计数法立刻停下来从源文件重新导入并在导入时把那列指定为文本格式。第三步清脏数据。用SUBSTITUTE或查找替换把空格、短横线、86前缀清掉确保所有号码都是纯数字。顺手用LEN(A2)拉一列辅助表看看有没有长度异常的凡是长度不等于 11 的行单独拎出来人工核对。第四步写公式。手机号列在右侧空白列写REPLACE(A2,4,4,****)身份证列写REPLACE(B2,7,8,********)向下填充。填充完抽查几行确认星号位置对得上。第五步固化结果。选中两个公式列CtrlC然后选择性粘贴—值把公式转成纯文本。这一步是为了防止公式被别人误改也避免了文件打开时因为重算而变慢。第六步清理。确认脱敏列无误后把原始号码列删除或隐藏删掉辅助的LEN列和中间过程列另存为交付版本。文件名里带上脱敏两个字避免和源文件混淆。5.2 交付前的自检清单发出去之前我一定会走一遍这几条用CtrlF搜索E确认没有残留的科学计数法单元格。随机挑 5 个号码用LEN()确认脱敏后长度都是 11 位或 18 位。检查删掉原始列之后表格里是否还有别的列能反推出完整号码比如备注栏里手写的号码、收件地址里的联系电话。确认文件属性里的作者公司等元信息是否需要清空。打开一次最终文件看首页打开时有没有警告提示。最后一条尤其容易漏。我见过有人把所有列都处理干净了结果在文档属性里留着整理者的姓名或者藏在批注里的原始号码没删。批注、文本框、隐藏行列、自定义视图这些都是脱敏时容易被跳过的角落值得单独检查一遍。6. 常见问题与排查实录这一节都是实打实遇到的问题我按出现频率排了序。6.1 身份证变成 1.10105E17末位还变成了 0这是最经典的一个。原因就是 2.4 节说的 15 位精度限制。要注意的是这个错误不可逆已经变形的数字改格式也救不回来。唯一的正确做法是把列设成文本格式后重新输入或重新导入。如果数据是从网页或其他系统复制的可以先粘贴到记事本里再从记事本复制到已经设好文本格式的 Excel 列中这样能确保它以文本形式落地。顺带说一句账号、订单号、流水号这类超过 15 位的编号都有同样的问题。处理任何长数字之前先设文本格式应该变成肌肉记忆。6.2 Excel 突然不能复制粘贴了这个问题在做脱敏时格外烦人因为整个流程都依赖复制粘贴。常见的几个原因和处理方式现象可能原因解决办法按 CtrlC 没反应单元格还在编辑状态按Esc退出编辑后再复制粘贴时报无法粘贴数据工作表被保护审阅—撤销工作表保护复制后粘贴内容不对剪贴板被其他程序占用关闭网银控件、翻译软件、远程桌面等只有这个文件不行加载项冲突文件—选项—加载项逐个禁用排查粘贴按钮是灰的工作表处于共享模式取消共享工作簿或改用其他方式数据量大时粘贴失败内存或剪贴板容量限制分批次粘贴每批几千行排查顺序我一般是这么走的先按Esc再看工作表是否被保护再关掉可能占用剪贴板的软件最后才去动加载项。因为动加载项影响面大放在最后。Mac 版 Excel 如果出现类似问题还要检查系统设置—隐私与安全性里给 Excel 的权限是否完整权限不全时剪贴板相关的操作会异常。6.3 其他高频问题速查问题原因处理公式显示#NAME?用了新版本函数如 LET换嵌套写法或用辅助列脱敏后长度变短了星号数量少于替换位数星号数与替换位数保持一致自定义格式不生效数据是文本型改用公式法或先转数值快速填充填到一半停了数据中有空行删空行或改用公式脱敏列无法 VLOOKUP结果变成文本键类型不匹配保留单独的关联键列全表变卡大量公式实时重算选择性粘贴为值星号显示成一片零自定义格式里星号没加引号写成000****0000单元格显示####列宽不够与脱敏无关拉宽列宽7. 几个踩过坑之后才明白的细节用格式隐藏和用公式隐藏差别真的不在操作难度上而在你以为你隐藏了这个心理陷阱上。我早年做过一次交付用自定义格式把一整列手机号变成了星号屏幕上看着特别干净发给对方之后对方截图问我这些号码怎么都是完整的。那次之后我给自己的规矩就变成了只要文件可能离开我的电脑一律用公式或者直接删列格式法只留给自己看的草稿表。另一个细节是脱敏范围别只盯着号码列。我见过备注栏里写着联系电话同身份证号的也见过地址栏里塞着完整手机号的还有藏在批注和文本框里的。这些地方不属于该列所以特别容易被跳过。现在我的习惯是交付前用CtrlF在整个工作簿里搜一遍手机号的号段特征比如搜13、15、18开头的数字串虽然会搜出一堆无关结果但花两分钟能挡掉大麻烦。还有个挺实用的思路如果你每个月都要处理结构一样的表别每次重新写公式把整个流程做成一个模板文件或者 Power Query 查询下次直接换数据源。我这几年最大的效率提升不是学会了某个函数而是把重复劳动变成了点一下刷新。最后说个关于备份的。我见过太多人是在覆盖完原数据之后才发现公式写错了位——比如把REPLACE(A2,4,4,****)写成了REPLACE(A2,3,4,****)结果所有的号都错了一位。这种错误在有备份的情况下只是重跑一次在没有备份的情况下就是重新去收集一遍客户资料。所以每次动手之前先复制一份文件这个动作无论数据多少行都别省。