跳到主要内容

身份证号提取出生日期与性别

中国身份证号是 18 位：前 6 位地址码，中间 8 位出生日期，第 17 位性别（奇男偶女），第 18 位校验码。用函数把它拆开，比手工抄一遍靠谱。

一、先备好样例​

A 列放身份证号，A2 为例：

510183199003072315

第一件事：把单元格格式改成"文本"。否则 Excel 会把它当数字，后三位变 0，还可能显示成科学计数法。已经变了的，只能重新录入或从源数据重导。

二、提取出生日期​

=TEXT(MID(A2,7,8),"0000-00-00")

得到 1990-03-07。想要真正的日期值（能参与计算、能排序），外面再套一层：

=--TEXT(MID(A2,7,8),"0000-00-00")

然后给单元格设成日期格式即可。-- 是文本转数值的简写，等价于 VALUE()。

三、提取性别​

第 17 位是性别位，取出来判断奇偶：

=IF(MOD(MID(A2,17,1),2)=1,"男","女")

15 位老身份证没有独立性别位，最后一位就是性别位，写 MID(A2,15,1)。

四、算年龄​

=DATEDIF(--TEXT(MID(A2,7,8),"0000-00-00"),TODAY(),"Y")

DATEDIF 是隐藏函数，输入时没有参数提示，但各版本都支持。

五、顺手校验号码​

校验位算法：前 17 位分别乘权重 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2，求和后对 11 取余，余数对应校验码 1 0 X 9 8 7 6 5 4 3 2。

不启用 VBA 也可以拼出来：

=IF(LEN(A2)<>18,"位数不对",
  IF(RIGHT(A2,1)=MID("10X98765432",
    MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*1,
      {7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,1),
  "通过","校验位错误"))

嫌长只做基础检查也够用：

=IF(LEN(A2)<>18,"位数不对",IF(ISERROR(--TEXT(MID(A2,7,8),"0000-00-00")),"日期段异常","格式正常"))

六、脱敏显示​

对外发名单时，保留前 4 位和后 4 位：

=LEFT(A2,4)&"**********"&RIGHT(A2,4)

或者用 REPLACE 按位替换：

=REPLACE(A2,7,8,"********")

脱敏一定用公式生成新列，别覆盖原始列。原数据被替换就找不回来了。

七、常见坑​

  • 号码变成 5.10183E+17：单元格是常规或数值格式，改成文本后重录。
  • MID 结果不对：前面有空格，先 TRIM。
  • 出生日期段是 19901332 这类非法日期：--TEXT 会报 #VALUE!，外面套 IFERROR。
  • 港澳台证件、护照不是 18 位，别硬套，先判断 LEN。

八、速查​

需求公式
出生日期（文本）=TEXT(MID(A2,7,8),"0000-00-00")
出生日期（日期值）=--TEXT(MID(A2,7,8),"0000-00-00")
性别=IF(MOD(MID(A2,17,1),2),"男","女")
年龄=DATEDIF(--TEXT(MID(A2,7,8),"0000-00-00"),TODAY(),"Y")
脱敏=LEFT(A2,4)&"**********"&RIGHT(A2,4)

出生日期段合法才能转成日期值，所以实际做表时建议先判 LEN，再判日期，最后才取值，顺序反了公式会长得很难看。