#excel#

我们的身份证号是一种特征号码的组合，有一定的规律，我们是可以通过身份证号读取出部分的信息的。

特征信息号意思是：前6位是地址码，代码了省市区县，再8位是出生日期，再3位是顺序位，最后一位是校验码；其中顺序为奇数是男性，偶数是女性。

了解了特征意思，我们就比较容易解读了，先说前6位，要依前6位数字准确地解读出籍贯1、地区信息，就得有一份准备的编码与省份地区的索引表；这个索引表可以到网上查找，很长的，我们就依110101为例，在表中可以查到代到中国北京市东城区；可以用VLOOKUP函数在索引表中读取出地区信息；=VLOOKUP(VALUE(LEFT(C4,6)),索引表!A:B,2,0)，其中的索引表是我建立的sheet2

函数说明：通过LEFT函数截取C4单 元格里的前6位，返回到“索引表“里查找A列下的相同代码，并将对应的B列值导入现在工作表D4单元格内；

2、接下来提取出生日期，=TEXT(1*TEXT(MID(C4,7,LEN(C4)/2.2),"0-00-00"),"yyyy年mm月dd日")提取出来返回2002年11月06日；=TEXT(1*TEXT(MID(C4,7,LEN(C4)/2.2),"0-00-00"),"yyyy-mm-dd")提取出来返回2002-11-06; 也可以=DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2))返回值是2002/11/06。

以上考虑的都 是18位的身份证号；如果是15位，要考虑到1-29默认是2001-2029年的问题；需要在这个身份证号的出生日期前加19，用下面这个公式 就行：

=--text(if(len(c4)=15,19,””)&mid(c4,7,6+if(len(c4)=18,2)),”#-0000”)

如果是1930年以后出生的，不用考虑2001-2029的问题；则可以简化为：

=--text(mid(c4,7,6+(len(C4)=18*2),”#-00-00”)

3、提取性别

用MOD求余数，用身份证号顺序号的最后一位数字除2，如果余数为1，则为男性，否则为女性：

=if(mod(right(left(c4,17)),2),”男”，“女”)

此公式中，对于18位的身份证号，LEFT（c4,17）函数返回前17位字符; 而对于15位的身份证号，LEFT(C4,17)只能返回前15位字符，然后RIGHT函数起作用，取出右边1个字符进行判断，从而得到结果；

Left(text,num_chars)与right(text,num_chars)意思是分别从左或从右从一个文本字符品开始取出指定数量的字符；text是指哪个单 元格，num_chars是取几个字符，必须大于等于0；此例 中，left函数截取了17个字符，这样如果是15位的就全部截取了，而18位的则只取前17位；这样代表性别的数字都 在截取后字符的最后一位；再用right截取最后一位数字就可以进行除2运算了，从而判断出男性还是女性；

4、最后说一下位数的转换：

我们的身份证号最后一位是校验码，是依据前17位的特征得到的数字，其值 为0-9或X，规则是依据1999年7月1日实施的GB11643-1999(公民身份号码)，依这规则升级18位的公式可以用：假设B3为15位，在C3中得到18位

=LEFT(B3,6)&19&RIGHT(B3,9)&LOOKUP(MOD(SUMPRODUCT(MID(LEFT(B3,6)&19&RIGHT(B3,9),ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0;1;2;3;4;5;6;7;8;9;10},{1;0;" X";9;8;7;6;5;4;3;2})

如果18位要转成15位，则简单多了，可以用：=IF(LEN(C4)=15,C4,LEFT(REPLACE(C4,7,2,)15))或着

=replace(left(c4,17),7,(len(c4)=18*2,)

以上两个都 可以实现由18位降为15位。

对了，小伙伴们觉得有用的话，请务必关注并收藏呀！后续大明哥持续不断地为大家分享Excel中实用的知识点和技巧。