HR必备!轻松提取出生日期、性别、年龄和地区代码

CIA 未知

  公民身份证信息在日常中的应用很多,在这18位身份证号码中,我们可以提取出很多相关信息,比如出生日期、性别、年龄还有所属区域代码等等。今天帮主就和大家讲一下提取这些信息的一些常用套路。

  1、提取出生日期

  方法一:函数法

  如下动图所示,输入公式:=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))

  解析:身份证号码第7位至第14位(即第7位开始的前4个数字代表出生年份,第11和12为数字代表出生月份,第13和第14为代表出生日期)是出生年月日信息,这里我们用到Mid函数线分别提取出身份证信息中的年、月和日,再结合Date函数即可。

  或者如下动图所示,输入公式:=TEXT(MID(A3,7,8),"0-00-00")

  解析:这里我们用Mid函数直接提取出年月日信息,再结合Text函数转换到指定的日期格式。

  方法二:分列法

  【数据】选项卡下的“分列”:固定宽度-在8位出生年月日前后建立分列线-保留代表生日的8位数字并设置成日期格式。具体操作如下动图:

  方法三:快速填充

  如果是多个身份证信息的提取,可以利用快速填充法(2016版),非常便捷!

  在生日列第1个空格中输入第1个身份证号码中的8位生日数字,然后按Ctrl+E键(快速填充快捷键)向下填充。最后设置自定义格式为:0-00-00

  2、判断性别

  如下动图所示,分别输入公式:

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

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

  解析:身份证号码中的第17位数字是用来判断男女性别,如果是奇数性别为男,偶数则为女。先用Mid函数提取出数字,结合Mod函数判断奇偶(Mod函数是求两个数相除的余数,若可以被2整除,余数0,为偶,否则为奇),最后再用If函数判断男女性别。

  3、计算年龄

  如下动图所示,输入公式:=YEAR(TODAY())-MID(A2,7,4);=YEAR(TODAY())-MID(A3,7,4)

  解析:先用Mid函数提取出生年份,当前年份减去出生年份即可。

  4、提取地区代码

  身份证号码信息中,前2位是省份代码,第3和第4位是城市代码,第5和6位是取现代码,我们可以利用Left函数进行数字提取,比如提取区县代码:=Left(A2,6),再根据代码在各区编码信息中查询对应的城市区县。

cia
       来源:Excel技巧精选,微信号:ExcelSkill,由香港注册会计师网【www.hkicpa.cn】整理发布,若需引用或转载,请注明来源!

向作者提问

  • 最新评论

验证码: 点击我更换图片
全部评论