Excel中如何将手机号中间四位替换为*？

Question

在处理客户数据时，常需对手机号进行脱敏处理以保护隐私。一个常见问题是：如何在Excel中将手机号中间四位替换为“*”？例如，将“13812345678”转换为“138****5678”。虽然Excel没有内置的数据脱敏函数，但可通过文本函数结合实现。使用LEFT、MID、RIGHT和REPT等函数组合，或借助 SUBSTITUTE 与 REPLACE 函数，可完成格式化替换。此外，部分用户因不熟悉文本截取逻辑或函数嵌套规则，易出现公式错误或结果不准确。如何编写简洁高效的公式，并确保适用于不同号段的手机号，是实际应用中的技术难点。

Answer 1

1. 手机号脱敏的背景与核心挑战

在处理客户数据时，常需对手机号进行脱敏处理以保护隐私。一个常见问题是：如何在Excel中将手机号中间四位替换为“*”？例如，将“13812345678”转换为“138****5678”。虽然Excel没有内置的数据脱敏函数，但可通过文本函数结合实现。

这一操作看似简单，但在实际业务场景中涉及大量非结构化或半结构化数据，数据源可能来自CRM系统、日志文件或数据库导出表，格式不统一。部分号码可能包含国家区号（如+86）、空格、横线或括号，增加了清洗和标准化的复杂度。

2. 基础函数解析与组合逻辑

• LEFT(text, num_chars)：从左侧提取指定字符数
• MID(text, start_num, num_chars)：从中间截取子字符串
• RIGHT(text, num_chars)：从右侧提取字符
• REPT(text, number_times)：重复生成特定字符
• LEN(text)：获取文本长度，用于动态判断

基于上述函数，可构建基础脱敏公式：

=LEFT(A1,3) & "****" & RIGHT(A1,4)

该公式适用于标准11位手机号，假设A1单元格存储原始号码“13812345678”，结果即为“138****5678”。

3. 高级公式的健壮性优化

原始号码	预处理后	脱敏结果
[phone]	13812345678	138****5678
139-1234-5678	13912345678	139****5678
(0)15012345678	15012345678	150****5678
188 1234 5678	18812345678	188****5678
17712345678	17712345678	177****5678
135****1234	1351234	#VALUE!
abc12345678	12345678	错误输入
13600001111	13600001111	136****1111
199 8888 7777	19988887777	199****7777
8613012345678	13012345678	130****5678

4. 综合清洗与脱敏一体化公式

为应对多样化的输入格式，需引入SUBSTITUTE与TEXTJOIN等函数进行清洗：

=LET(
    cleanNum, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"+",""),"-","")," ",""),"(",""),
    cleanNum, SUBSTITUTE(cleanNum,")",""),
    len, LEN(cleanNum),
    pureNum, IF(LEFT(cleanNum,2)="86", MID(cleanNum,3,len-2), cleanNum),
    IF(LEN(pureNum)=11, LEFT(pureNum,3)&"****"&RIGHT(pureNum,4), "无效号码")
)

此公式使用LET函数提升可读性与性能，先去除常见干扰字符，再判断是否以“86”开头并剔除，最后执行脱敏逻辑。

5. 流程图：手机号脱敏处理流程

graph TD
    A[输入原始手机号] --> B{是否含特殊字符?}
    B -- 是 --> C[使用SUBSTITUTE清洗]
    B -- 否 --> D[直接进入判断]
    C --> D
    D --> E{是否以86开头?}
    E -- 是 --> F[MID截取后11位]
    E -- 否 --> G[保留原值]
    F --> H
    G --> H[判断长度是否为11]
    H -- 是 --> I[LEFT+****+RIGHT脱敏]
    H -- 否 --> J[返回错误提示]
    I --> K[输出脱敏结果]
    J --> K

6. 实际应用场景扩展

在企业级数据治理中，此类脱敏逻辑常被封装为：

1. Excel模板中的命名公式（Name Manager）
2. Power Query中的自定义M函数
3. VBA宏自动化批处理模块
4. 集成至Power BI数据模型作为DAX计算列
5. 导出为Python脚本用于大规模ETL任务

此外，还可结合条件格式高亮未脱敏字段，或通过数据验证限制原始数据可见范围，形成完整的隐私保护机制。