excel身份证号码提取户籍所在地_知道经验

身份证号非常重要，里面隐藏了很多信息，这些信息发生泄露，就很危险，今天我们用Excel来提取身份证号里面的各种信息
1、从身份证中提取地区，省市县
身份证号的前6位中就隐藏着这些信息，1-2位表示省，第3-4位表示市，第5-6位表示县，我们首先下载一份身份证地区码

然后我们对A列的身份证号进行提取数字，然后进行查找匹配地区表
在B2查找省份输入公式：
=VLOOKUP(LEFT($A2,2)&”*”,身份证地区码!$A:$B,2,0)
在C2查找城市输入公式：
【excel身份证号码提取户籍所在地】
=VLOOKUP(LEFT($A2,4)&”*”,身份证地区码!$A:$B,2,0)
在D2查找区县输入公式：
=VLOOKUP(LEFT($A2,6),身份证地区码!$A:$B,2,0)

身份证号是模拟的，倒数34位用X隐藏
2、提取生日
身份证号的第7位至第13位是表格出生的年月日，在E2单元格中输入公式：
=TEXT(MID(A2,7,8),”0-00-00″)

3、提取年龄
已经把出生年月日提取出来了，我们只需要使用
=DATEDIF(E2,TODAY(),”Y”)来计算年龄

4、提取性别
身份证号的倒数第2位，也就是第17位，如果是奇数，那么为男性，如果为偶数，则为女性，所以在G2单元格中输入公式：
=IF(MOD(MID(A2,17,1),2),”男”,”女”)

5、提取生肖
每12年生肖进行一次的轮换，所以使用公式：
=MID(“猴鸡狗猪鼠牛虎兔龙蛇马羊””,MOD(MID(A2,7,4),12)+1,1)

6、提取星座
首先我们要知道星座的时间区间如下所示：

根据上面的规则，我们在Excel里面K:L列建立辅助项，如下所示：

然后我们再使用VLOOKUP函数的模糊查找功能，输入公式：
=VLOOKUP(MID(A2,11,4)*1,K:L,2,1)

如果不想有辅助列，那么数据中间的数据源K:L可以使用一个数组来替换
输入的公式是：
=VLOOKUP(MID(A2,11,4)*1,{0,”摩羯座”;120,”水瓶座”;219,”双鱼座”;321,”白羊座”;420,”金牛座”;521,”双子座”;622,”巨蟹座”;723,”狮子座”;823,”处女座”;923,”天秤座”;1024,”天蝎座”;1123,”射手座”;1222,”摩羯座”},2,1)
所以仅仅根据一个身份证号，比如：33018319931224XX41 ，就可以知道这个人是浙江杭州市富阳区的25岁的女生，属鸡，摩羯座，生日是1993年12月24号。

更多精彩内容，可以在头条主页查看~
身份证号码中隐藏了非常多的个人信息，例如户籍地、出生日期、性别等。如下图所示，我们该如何通过身份证号码将上述信息给提取出来。

思路：身份证号码前6位代表的是户籍地信息，第7到14位表示的出生日期，倒数第二位表示的是性别信息。我们要得到户籍地，只需将前6位数字提取出来，然后到户籍地数据库中查询并返回相应的户籍地信息即可。要得到出生日期信息，我们只需将第7到14位数字提取出来，然后转化为真正的日期即可；要得到性别信息，只需要判断倒数第二位数字的奇偶性即可。
一、快速得到户籍地信息的技巧
我们在百度文库上搜索“行政区划代码”即可免费下载的行政区划代码，下载下来打开备用，你可以将其复制到身份证号码所在的工作表里，如下图所示。

接下来我们就可以利用查询户籍地信息了。
1.提取身份证号码前6位数字

=Left(F2,6)

2.利用提取到的身份证号码前6位数字到行政区划数据中去查询

=vlookup(Left(F2,6),$A:$B,2,0)

当然，利用其它的函数照样可以搞定户籍地信息的查询，比如：
=LOOKUP(LEFT(F2,6),$A:$B)
=INDEX(A$2:B$3526,MATCH(LEFT(F2,6),A$2:A$3526,),2)
……
二、利用简单的IF函数确定身份证中的性别信息
身份证号码中的倒数第二位数字代表性别，奇数为男，偶数为女。首先我们可以文本函数提取倒数第二位数字，然后判断其奇偶性，从而得出性别信息。
1.提取身份证号码倒数第二位

=left(right(F2,2),1)
=mid(F2,17,1)

2.判断提取到的数字的奇偶性

=isodd(–mid(F2,17,1))

3.根据判断结果返回性别信息
=IF(ISODD(–LEFT(RIGHT(F2,2))),”男”,”女”)
即可得到结果。

三、快速从身份证号码得到出生日期
要从身份证号码提取出生日期，方法有很多。比如函数法、分列法以及快速填充法等。在这里我就介绍最简单的一种提取出生日期的技巧：分列法。
技巧：
选中F2:F29单元格区域，按下Alt+A+E组合键打开文本分列向导对话框，选择固定宽度，单击下一步，在打开的对话框中身份证号码的第6位数字后及第15位数字前单击单击一下建立两条分割线，然后单击下一步，单击分列线前半部分，勾选不导入此列，在第二条分列线后的部分，勾选不导入此列，然后选中两条分列线的中间部分，勾选日期，目标区域选择I2 ，单击完成即可。