#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中实用的知识点和技巧。