月底入职高峰期，HR小王盯着电脑屏幕上一百多个新员工的身份证号，头都大了。

"这180个身份证号，要挨个填出生日期、性别、年龄、籍贯……"

她手动算了前5个，第6个就开始眼花，180个身份证号18位数字，人工拆解至少干到晚上10点。而且手动输入出错率极高——性别填反、生日月份写错，月底对账时才发现，尴尬到想钻地缝。

其实，Excel有5个文本函数——LEFT、RIGHT、MID、FIND、TEXT，就像五把精准的手术刀，只要身份证号/手机号/地址格式是规律的，就能自动把关键信息"切"出来。180条数据3分钟搞定，永远不出错。

今天这篇文章，手把手教你组合使用这5个函数，涵盖身份证信息提取、手机号脱敏分段、地址省市拆分，全是HR和行政日常高频场景。


核心技巧
招式一：LEFT + RIGHT —— 快速提取地区码和校验码

📌 场景
公司要按省份统计员工分布做区域补贴，需要从身份证号前6位（地区码）识别员工籍贯地。另外做数据校验时也要提取最后一位（校验码X），核对身份证是否录入正确。

🔧 步骤（≤5步，每步≤20字）
打开身份证号表格（A列已设文本格式）
在B7输入=LEFT(A7,6)提取前6位地区码
下拉公式填充全部地区码
在C7输入=RIGHT(A7,1)提取校验码
下拉公式提取全部校验码
📐 公式解析
=LEFT(文本, 提取字符数)
=RIGHT(文本, 提取字符数)
=LEFT(A7,6)从A7单元格左边取6个字符 → 提取地区码"110101"（对应北京市东城区）
=RIGHT(A7,1)从A7单元格右边取1个字符 → 提取最后一位（校验码，可能是X）
⚡ 兼容性
LEFT和RIGHT是Excel最基础的文本函数，所有版本通用（2003/2007/2010/2013/2016/2019/365/WPS），无兼容问题。

⚠️ 踩坑提醒

LEFT和RIGHT提取出来的是文本格式，不是数字！如果你要对提取结果做数值运算，会报错。需要先转数字：=VALUE(LEFT(A7,6)) 或直接用 --LEFT(A7,6)（两个负号强制转数值）。
我第一次用LEFT提取地区码后想用IF判断，结果#VALUE! 弹了一整列——条件判断"110101">"100000"比的是字符串，不是数字，逻辑方向完全不一样。



招式二：MID —— 从身份证号提取完整出生日期和性别

📌 场景
员工档案表需要"出生日期"和"性别"两列，全部从身份证号自动获取。手工填容易把6月写成7月，男写成女，180个人核对到崩溃。

🔧 步骤（≤5步，每步≤20字）
在B7输入=MID(A7,7,8)提取出生日期文本
在C7输入=DATE(MID(A7,7,4),MID(A7,11,2),MID(A7,13,2))转真实日期
下拉B7:C7公式填充全部行
在D7输入=IF(MOD(MID(A7,17,1),2)=0,"女","男")判断性别
下拉D7公式填充全部性别

📐 公式解析
=MID(文本, 起始位置, 提取字符数)
MID从文本的第N个字符开始，取M个字符：
=MID(A7,7,8)：从第7位取8个字符 → "19900520"（18位身份证的第7-14位是出生年月日）
=DATE(MID(A7,7,4), MID(A7,11,2), MID(A7,13,2))：把年月日数字拆出来，用DATE函数合成真正的日期格式。MID(A7,7,4)=年，MID(A7,11,2)=月，MID(A7,13,2)=日
=IF(MOD(MID(A7,17,1),2)=0,"女","男")：第17位是性别码，奇数=男，偶数=女。MOD(数字,2)取余数，余0=偶数→女

⚡ 兼容性
MID、DATE、MOD函数全版本通用，无兼容问题。

⚠️ 踩坑提醒

MID的起始位置从1开始算，不是从0！很多人第一次用MID会下意识从0开始数，结果取少了1位。身份证第1位是地区码，第7位才是生日——=MID(A7,7,8) 从7开始，取8位。


另外，15位老身份证（现在已经很少了）的生日在第7-12位，只有6位数字。用 =IF(LEN(A7)=15, MID(A7,7,6), MID(A7,7,8)) 可以兼容新旧格式

最大的坑：Excel只能精确存储15位数字。如果你直接把18位身份证号输入Excel而没有提前把列格式设为"文本"，最后3位会变成000。这时候MID拆出来的数据全错！输入身份证号前，必须先把列格式设为"文本"，或在输入时前面加单引号 '。


招式三：FIND + MID —— 从地址中自动拆分省、市、区

📌 场景
行政做通讯录，要从"北京市朝阳区建国路88号"这类完整地址中，自动拆分出省、市、区三列，方便按城市分组寄快递。

🔧 步骤（≤5步，每步≤20字）
在B7输入=MID(A7,1,FIND("省",A7))提取省
在C7输入=MID(A7,FIND("省",A7)+1,FIND("市",A7)-FIND("省",A7))提取市
在D7用类似公式提取区（找"区"或"县"位置）
下拉B7:D7填充全部地址
用IFERROR包裹，处理直辖市（北京无"省"）

📐 公式解析
=FIND(要找的文本, 在哪里找, [从第几个开始])
FIND函数返回某个字符在文本中的位置（第几个字），区分大小写。配合MID就能动态定位提取：
=MID(A7,1,FIND("省",A7))：先找到"省"在第几个字符（比如"广东省深圳市"→"省"在第4位），然后MID从第1位取到"省" → "广东省"
直辖市如何处理？
北京、上海、天津、重庆的地址格式是"北京市朝阳区……"，没有"省"字，FIND("省",A7)会报#VALUE!。用IFERROR兜底：
省 = IFERROR(MID(A7,1,FIND("省",A7)), MID(A7,1,FIND("市",A7)))
市 = IFERROR(MID(A7,FIND("省",A7)+1,FIND("市",A7)-FIND("省",A7)), MID(A7,1,FIND("市",A7)))
逻辑：找到了"省"就按省来拆；没找到（直辖市），直接拆到"市"。区县同理，找"区"或"县"的位置。

⚡ 兼容性
FIND、MID、IFERROR全版本通用（IFERROR在Excel 2007+版本可用，2003版用 =IF(ISERROR(...), 备用值, 原公式) 替代）。

⚠️ 踩坑提醒

FIND区分大小写！如果你要找"A"但数据里是"a"，FIND找不到就报#VALUE!。不区分大小写的替代方案是SEARCH函数（语法和FIND完全一样，但不区分大小写，且支持通配符 ? 和 *）。


另一个血泪教训：地址格式千奇百怪——有人写"广东深圳"，有人写"广东省深圳市"。如果全公司地址格式不统一，FIND会大面积报错。建议先用数据验证统一地址格式，再用FIND+MID拆分。我的做法是做一个地址标准录入模板，用下拉菜单选省市，从源头解决格式问题。


招式四：TEXT + LEN + & —— 手机号脱敏、校验和格式化

📌 场景
HR做员工花名册要公开部分信息，手机号需要做三件事：①脱敏保护隐私（中间4位变****）②分段显示方便阅读（138-0000-5678）③批量校验位数是否正确（防止录入错误）。

🔧 步骤（≤5步，每步≤20字）
在B2输入=LEN(A7)先校验手机号位数（应为11）
在C2输入=LEFT(A7,3)&"****"&RIGHT(A7,4)脱敏
下拉B2:C2填充全部数据
在D2输入=TEXT(--A7,"000-0000-0000")分段显示
下拉D2填充全部
📐 公式解析
① 位数校验：
=LEN(A7) 返回文本的字符数。手机号应为11位。结合IF做自动标注：=IF(LEN(A7)=11,"正确","位数不对")。合并单元格+条件格式，异常行自动标红。
② 手机号脱敏：
=LEFT(A7,3)&"****"&RIGHT(A7,4)
& 是文本连接符，把三个部分拼在一起：
LEFT(A7,3) → "138"（前3位）
"****" → 固定掩码
RIGHT(A7,4) → "5678"（后4位）
拼接结果 → "138****5678"
③ 手机号分段显示：
=TEXT(--A7,"000-0000-0000")
TEXT函数把数字按指定格式转为文本：
0是占位符（必须显示数字，位数不够前面补0）
--A7两个负号强转数值（因为TEXT要求源数据是数字，文本格式报错）
"000-0000-0000" 格式 → "138-0000-5678"

⚡ 兼容性
TEXT、LEN函数全版本通用。& 连接符全版本通用。

⚠️ 踩坑提醒
TEXT("000-0000-0000") 里的 0 是数字占位符，不要写成字母O！新手经常把格式代码写成 "OOO-OOOO-OOOO"（字母O），结果格式不生效。


TEXT格式代码中 0 是强制占位（位数不够前面补0），# 是可选占位（不补0）。比如座机号010开头的，用 # 会吞掉前导0（变成"10-xxxx-xxxx"），必须用 0。

手机号如果是文本格式（单元格左上角绿三角），TEXT直接返回原文本不格式化。必须用 --A7 转数字：两个负号负负得正，Excel在运算过程中自动完成文本→数值转型，是最快的转换方法。


进阶联动：身份证号一键提取全部信息（四招串联）

把前面4招串联起来，一个身份证号输入进去，同一行自动输出6种信息：
提取项
公式（填入对应列）
说明
地区码
=LEFT(A7,6)
招式一，前6位
出生日期
=DATE(MID(A7,7,4),MID(A7,11,2),MID(A7,13,2))
招式二，MID拆年月日
年龄
=DATEDIF(DATE(MID(A7,7,4),MID(A7,11,2),MID(A7,13,2)),TODAY(),"Y")
DATEDIF算年份差
性别
=IF(MOD(MID(A7,17,1),2)=0,"女","男")
招式二，第17位
校验码
=RIGHT(A7,1)
招式一，最后1位
籍贯
=VLOOKUP(LEFT(A7,6),籍贯码表!$A:$B,2,0)
招式一+招式三联动，查码表

操作关键：这6个公式分别填入B2~G2单元格，然后同时选中B2:G2，双击右下角填充柄——六列一起下拉，180人的信息一次性全部自动填充。原来手工做一天，现在3分钟。
配上条件格式（年龄>60标灰退休提醒、性别列男女分色），这表格就是HR的"身份证一键解析神器"。


高频场景
场景1：HR花名册信息脱敏
公司内部公示花名册时，手机号中间4位自动变****（用LEFT+RIGHT+&拼接），身份证号只显示前6后4（=LEFT(A7,6)&"****"&RIGHT(A7,4)），保护隐私合规。

场景2：行政快递地址按城市筛选
从完整地址中用FIND+MID拆出城市列，再用筛选功能（Ctrl+Shift+L）一键只看"深圳"的发货单，批量打印快递面单。

场景3：财务工资条校验身份证位数
用LEN函数批量检查身份证号是否为18位（=IF(LEN(A7)<>18,"位数异常","")），异常行用条件格式标红。财务发工资条前必做这一步，防止身份证号录错导致个税申报异常。

避坑指南

坑1：身份证号超过15位精度丢失（P0致命）
Excel只能精确存储15位数字。18位身份证号直接输入，后3位自动变000。解决：输入前选中列 → 右键"设置单元格格式" → 选"文本" → 再粘贴身份证号。或者输入时前面加单引号 '（如 '110101199001011234）。

坑2：LEFT/RIGHT/MID提取的是文本不是数字
三个函数返回的都是文本格式，做加减乘除前必须转换。解决：前面加两个负号 -- 或包一层 VALUE()。如 =--LEFT(A7,6) 才能参与数值比较。

坑3：FIND找不到目标返回#VALUE!
地址中如果没有"省"字，=FIND("省",A7) 直接报#VALUE!。解决：一律用 =IFERROR(FIND("省",A7),"无") 或 =IFERROR(MID(...),"手动填写") 兜底，不要让公式裸奔。（Excel 2003用户用 =IF(ISERROR(...), "备用值", 原公式) 替代IFERROR。）

坑4：TEXT格式化要求源数据是数字
手机号如果是文本格式（单元格左上角绿三角），TEXT不生效。解决：先转数字 =TEXT(--A7,"000-0000-0000")。

坑5：MID位置从1开始数，不是0
很多人习惯编程从0开始计数，用 =MID(A7,6,8) 提取生日结果从第6位开始，少取了1位（应该是第7位）。记住：Excel所有位置函数都是从1开始。


引流

今天讲的5个文本函数，我整理了一份**《Excel文本函数学习模板》**——包含全部函数语法、身份证提取公式、地址拆分公式、手机号脱敏公式，打开就能照着用。

👉 在公众号「华杰科技工作室」中后台回复【资料】，速查卡和模板一起发给你。

你用Excel提取过哪些奇葩格式的数据？身份证号踩过什么坑？评论区聊聊