这是一篇整合身份证号码提取的总结教程！
只需在Excel中输入身份证号码即可得到以下数据：包含校验输入的正确性，提取年龄、性别、出身年月、所在地区、生肖、星座、生日提醒。
18位身份证的号段含义如下：
前2位表示所属省份代码，3~6位表示所属城市和区县代码，7~14位表示出生日期，格式为YYYYMMDD，15~17位是个人顺序码，其中17位可以标识性别，奇数表示男性，偶数表示女性，第18位是校验码，由前17位通过计算求得。
输入18位身份证显示4.45121E+17，WHY？
这是科学计数法，E+17是10的17次方。默认情况下，数字超过12位就显示科学技术法，数字超过15位后面都显示0。如图所示。
选择A列，将单元格格式设置为文本，再重新输入身份证即可。
设置好后，又有一个问题。如何才能保证身份证刚好是18位，同时还得符合身份证的所有条件，前17位必须是数字，第18位是X或者是数字。
18位可以用
=LEN（A2）=18
前17位是数字
=ISNUMBER(-LEFT(A2,17))
第18位是数字或者字母X
=OR(ISNUMBER(-RIGHT(A2)),RIGHT(A2)="X")
综合3个条件，可以进行数据有效性设置。
选择A2:A5，设置数据有效性（注：Excel2013版叫数据验证），在“允许”下拉列表框中选择“自定义”选项。在“公式”文本框输入下面的公式，点击“确定”按钮。
=AND(LEN(A2)=18,ISNUMBER(-LEFT(A2,17)),OR(ISNUMBER(-RIGHT(A2)),RIGHT(A2)="X"))
数据有效性对于已经输入好的身份证无效，但可以把不满足条件的圈出来。对设置好数据有效性后进行“圈释无效数据”。
把无效的数据重新更改即可，然后输入正确的身份证，以后如果输入不正确的话，会提示错误，直到你输入正确为止。如图所示。
关于身份证的准备工作已经做好，后面是获取各种信息的关键时刻。
如图所示，是一份详细的地区码。
通过提取码可以查询到省份跟地区。
省份就是提取身份证前2位，然后去地区码查询对应值。
=VLOOKUP(LEFT(A2,2),地区码!A:B,2,0)
地区就是提取身份证前6位，然后去地区码查询对应值。
=VLOOKUP(LEFT(A2,6),地区码!A:B,2,0)
出生日期就是从身份证第7位开始提取8位，将提取到的数字转换成0-00-00这种形式。
=--TEXT(MID(A2,7,8),"0-00-00")
性别就是第15-17位，奇数是男，偶数是女。
=IF(MOD(MID(A2,15,3),2),"男","女")
除了上面介绍的，通过出生日期还可以获取一系列问题：周岁、生肖、生日提醒、星座等。现在就以这4个常见问题再进行说明。为了方便，出生日期就引用D2单元格。
周岁
=DATEDIF(D2,NOW(),"y")
生肖
=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(TEXT(D2,"[$-130000]e")-4,12)+1,1)
TEXT(D2,"[$-130000]e")的作用就是将日期转换成阴历年份。
生日提醒
=TEXT(TEXT(D2,"m-d")-TEXT(NOW(),"m-d"),"还有0天生日;;今天生日")
先将日期转变成月日再进行相减，大于0就显示还有多少天生日，小于0就不显示，0就是今天生日。
星座
=LOOKUP(--TEXT(D2,"m.dd"),{0,"摩羯座";1.21,"水瓶座";2.2,"双鱼座";3.21,"白羊座";4.21,"金牛座";5.22,"双子座";6.22,"巨蟹座";7.24,"狮子座";8.24,"处女座";9.24,"天秤座";10.24,"天蝎座";11.23,"射手座";12.23,"摩羯座"})
这个看着很长，其实很简单，只要获取星座对应表就可以。
经过了N步处理，最终效果如图所示。
如果你觉得教程不错，请分享给你的朋友们，独乐乐不如众乐乐！
本文来自 部落窝教育 微信公众号：excel教程 的分享
学习更多excel技巧教程请关注我们的微信公众号：excel教程