EXECL 根据身份证号提取数据

    |     2012年10月31日   |   应用心得   |     0 条评论   |    1511

 

Excel中经常使用到身份证号,更多的时候还需要提取其中的相关数据,比如提取出生年月,性别,年龄,这里说说几个常用的示例。

 

先简单说说身份证号,由15位(一代证)或18位(二代证)数组成的,其中前6位是区位识别码,代表着省、地区、县市等信息。15位身份证号从第7位到第12位是出生年月日,年份用的是2位数,最后三数们前二位是身份识别码,后一位是性别码。18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数,最后四数们为身份识别码,倒数第二为性别码。特别要注意的是,所有的提取工作都是基于身份证号的,所以,首先要确定身份证号是正确的。

 

一、提取出生年月(15位18位自动调整):

=IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,6)),IF(LEN(A1)=18,CONCATENATE(MID(A1,7,8)),"错误"))

说明:首先用 逻辑判断函数 IF() 和字符个数计算函数 LEN() 判断 A1 单元格内的身份证号位数:

如果是15位,则从第 7 位开始提取,取 6 位数,并且在这6位数前加 19。如15位身份证号 100001800808001 ,提取后为 19800808。

如果是18位,则从第 7 位开始提取,取 6 位数。如18位身份证号 10000119800808001 ,提取后为 19800808。

如果身份证号既不是15位也不是18位,工式单元格将显示"错误"

如果只要“年-月”格式,公式可以修改成如下(当然,这里面的“-”,你也可以自己更换):

=IF(LEN(A1)=15,CONCATENATE("19"&MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2)),IF(LEN(A1)=18,CONCATENATE(MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)),"错误"))

 

 

二、提取性别(15位18位自动调整):

=IF(LEN(A1)=15,IF(MOD(VALUE(RIGHT(A1,3)),2)=0,"女","男"),IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,15,3)),2)=0,"女","男"),"身份证错"))

说明:先判断15或18位,取性别码识别男女。

 

 

三、根据身份证号码求年龄(15位18位自动调整):

=IF(LEN(A1)=15,YEAR(NOW())-1900-VALUE(MID(A1,7,2)),IF(LEN(A1)=18,YEAR(NOW())-VALUE(MID(A1,7,4)),"身份证错"))

说明:这里NOW()是取的当前系统时间,如果不正确,先核对一下当前的系统时间。

回复 取消