excel身份证号提取出生日期公式(身份证提取性别年龄)

关注我,私信回复关键词【身份证信息提取工具】,获取小西制作好的身份证信息提取工具!

excel身份证号提取出生日期公式(身份证提取性别年龄)(1)

excel身份证号提取出生日期公式(身份证提取性别年龄)(2)

各位帅哥美女,你们好,我是小西!身份证号码信息提取是很多职场人经常会遇到的情况,可是你知道怎么样才能快速完成各种信息的提取吗?今天小西就用一篇文章告诉你,Excel如何从身份证号码中提取,性别、出生日期、实岁、虚岁、生肖、星座,以及生日到期提醒、身份证查找重复值。

excel身份证号提取出生日期公式(身份证提取性别年龄)(3)

身份证号码不得不说的常识

身份证号码各位数字都代表不同的含义,想要顺利提取各种信息,就必须了解各位数字所代表的意义,详细情况如下:

第1-2位是省、自治区、直辖市代码

第3-4位是地级市、盟、自治州代码

第5-6位是县、县级市、区代码

第7-14位代表出生年月日

第15-17位为顺序号,其中17位数字男为单数﹐女为双数

第18位为校验码,0-9和X。

1、身份证号码录入

Excel中,当单元格输入的数字位数超过11位时,Excel就会自动以科学记数形式显示。同时Excel处理数字的精度为最大为15位,当单元格中输入的数字超过15位的部分将被自动变成0。这也是我们直接输入身份证号码显示错误的原因。

想要正确录入身份证号码,可以将单元格格式变为文本,或者在身份证号码输入前面加上一个英文状态下的单引号“'”。

excel身份证号提取出生日期公式(身份证提取性别年龄)(4)

2、提取出生日期

Excel提取出生日期就是从身份证号码第7位数字开始提取,提取8位数字,并将这8位数字变为0-00-00这种格式。那么我们只需要在单元格中输入公式

=TEXT(MID(B4,7,8),"0-00-00")

excel身份证号提取出生日期公式(身份证提取性别年龄)(5)

3、提取实岁

这种计算方式就是计算整年份差,不到生日的时候减去1岁。比如当前日期为2021年8月13日,出生日期为2019年8月13日,计算的年龄结果为1岁。

想要获取实岁只需要在单元格中输入公式

=DATEDIF(--TEXT(MID(B4,7,8),"0-00-00"),TODAY(),"y")

excel身份证号提取出生日期公式(身份证提取性别年龄)(6)

4、提取虚岁

忽略日期,仅计算年份差。这种年龄计算方式就比较简单了。直接用2021减去出生年份就可以啦。

=2021-MID(B4,7,4)

excel身份证号提取出生日期公式(身份证提取性别年龄)(7)

5、提取性别

身份证号码中第17位数字,奇数代表男性,偶数代表女性。首先,我们用MID函数将第17位数字提取出来,然后利用MOD函数判断奇偶(能否被2整除),最后用if函数判断男女。

=IF(MOD(MID(B4,15,3),2),"男","女")

excel身份证号提取出生日期公式(身份证提取性别年龄)(8)

6、提取生肖

拖动查看完整公式

=IF(ISBLANK(B4),"",MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(TEXT(MID(B4,7,8),"0000-00-00"))-1900,12) 1,1))

excel身份证号提取出生日期公式(身份证提取性别年龄)(9)

7、提取星座

=LOOKUP(--MID(B4,11,4),{101,"摩羯";120,"水瓶";219,"双鱼";321,"白羊";420,"金牛";521,"双子";621,"巨蟹";723,"狮子";823,"处女";923,"天秤";1023,"天蝎";1122,"射手";1222,"摩羯"})&"座"

excel身份证号提取出生日期公式(身份证提取性别年龄)(10)

8、生日到期提醒

插入辅助列计算出生日期,然后在单元格输入下方公式,就可以完成了。数字为负数,表示当年生日已经过去,整数代表距离生日的天数。

=IF(B4="","",TEXT(C4,"m-d")-TODAY())

excel身份证号提取出生日期公式(身份证提取性别年龄)(11)

9、身份证查找重复值

输入公式=IF(COUNTIF(B:B,B4&"*")>1,"重复","不重复")

excel身份证号提取出生日期公式(身份证提取性别年龄)(12)

好啦,用Excel身份证号码信息提取的技巧就讲到这里啦!你学会了吗?看完文章,别忘了点个,支持一下哟~ღ( ´・ᴗ・` )比心,这样小西才会有更多的动力来写这方面的干货哦,下期再见!

关注我,私信回复关键词【身份证信息提取工具】,获取小西制作好的身份证信息提取工具!

工具亮点:

1、设置身份证号码录入重复提醒及自动检测身份证号码是否为18位,不足或超/18位将自动提醒。

2、录入身份证号码自动获取籍贯、性别、虚岁、实岁、星座、生肖、及生日倒计时。

3、生日到期提醒,让您方便了解每位同事生日

excel身份证号提取出生日期公式(身份证提取性别年龄)(13)

excel身份证号提取出生日期公式(身份证提取性别年龄)(14)

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页