Excel根据身份证号码提取性别、出生年月日及年龄

wuyc

在个人信息处理中，经常需从身份证号中提取性别、出生年月日及年龄。

一代身份证号是15位，二代身份证号是18位，应根据情况采取不同的处理方法，否则会出错。

如：A列为身份证号码，要在B、C、D列分别提取出该人的性别、出生年月日、年龄。

一、利用函数

1、提取性别：

在B2单元格中输入公式：

=IF(MID(A2,17,1)/2=TRUNC(MID(A2,17,1)/2),"女","男")

说明：

此公式适用于全部为二代身份证号（18位）

若同时包含有一代身份证号（15位）和二代身份证号（18位），可将公式改为：

=IF(LEN(A2)=18,IF(MID(A2,17,1)/2=TRUNC(MID(A2,17,1)/2),"女","男"),IF(MID(A2,15,1)/2=TRUNC(MID(A2,15,1)/2),"女","男"))

把B2单元格向下填充，在B列完成所有A列身份证性别的提取。

2、提取出生年月日

公式1：

在C2中输入公式：

=MID(A2,7,4)&"."&MID(A2,11,2)&"."&MID(A2,13,2)

此公式适用于全部为二代身份证号，输出的出生年月日中间以“.”隔开。

公式2：

若既含有一代身份证又有二代身份证，年月日中间以“.”隔开。公式改为：

=IF(LEN(A2)=18,MID(A2,7,4)&"."&MID(A2,11,2)&"."&MID(A2,13,2),"19"&MID(A2,7,2)&"."&MID(A2,9,2)&"."&M|ID(A2,11,2))

若要使出生日期格式为：****年**月**日，则把“.”依次改为“年”、“月”、“日”。

把C2单元格向下填充，在C列完成所有A列身份证的出生年月日的提取。

公式3

=IF(LEN(H1)=15,19,"")&MID(H1,7,6+(LEN(H1)=18)*2)

解析：

LEN(H1)求的是身份证号码长度，

IF的作用是判断是否是15位的旧身份证信息，若是，就是二个数年份，前面要加上19，否则不用加。

LEN(H1)=18会产生两种结果：

如果身份证号有18位，则等式成立，6+(LEN(H1)=18)*2则相当于6+ture*2=6+2=8,在计算中逻辑值ture转换为1，6+1*2=8（18位新身份证的生日有8个数字）

如果身份证号不等于18位，则结果为0，6+0*2=6（15位新身份证的生日只有6个数字）

MID(H1,7,6)就是在H1单元格字符串中从第七个字符开始取6个字符，就是将身份证中代表日期的那段给取出来。如果是18位新身份证，则取6+2，即8个数字，否则只取6个数字。

公式4：

=IF(L14="","",TEXT(MID(L14,7,LEN(L14)/2-1),(LEN(L14)=15)*19&"0-00-00"))

公式5：

=IF(F4<>"",TEXT(LEN(F4)=15)*19&MID(F4,7,6+(LEN(F4)=18)*2),"#-00-00")+0,)

（如果F4不为空，那么，就显示为：如果F4的字符串总数为15位，就等于19加上(并接)F4的从第7个字符串开始取，如果F4的字符串个数为18，即取(6+2)个，否则取(6+0)个，并格式化为日期格式。后面加0，将日期转换为数值。）

公式6

=MID(H1,7,LEN(H1)/2-1)

下面这个是把年月日分开的公式

=TEXT(MID(H1,7,LEN(H1)/2-1),"0-00-00")

3、计算年龄

计算截止2021年8月1日时的年龄，在D2中输入公式：

=DATEDIF(TEXT(MID(A2,7,8),"#-00-00"),"2021-08-01","y")

计算编辑时的年龄，公式改为：

=DATEDIF(TEXT(MID(A2,7,8),"#-00-00"),TODAY（）,"y")

说明：

TODAY()为返回系统当前日期的函数。

上面两个公式只适用于二代身份证。若既含有一代身份证又有二代身份证，公式改为：

=IF(LEN(A2)=18,DATEDIF(TEXT(MID(A2,7,8),"#-00-00"),TODAY（）,"y"),DATEDIF("19"&TEXT(MID(A2,7,6),"#-00-00"),TODAY（）,"y")

二、用wps公式中的插入函数项来提取15位或者18位身份证号中的出生年月日（不用写公式）

主要步骤：

1、点击显示结果的单元格。

2、点击公式菜单下的插入函数选项。

3、点击常用公式项。

4、输入或点击原数据（身份证号码）所在的单元格位置

5、确定，即可提取出出生年月日

下面是以上步骤的gif动画演示，以及wps运算结果的展示操作：

D5单元格内的公式为：

=DATE(MID(A5,7,VLOOKUP(LEN(A5),{15,2;18,4},2,0)),MID(A5,VLOOKUP(LEN(A5),{15,9;18,11},2,0),2),MID(A5,VLOOKUP(LEN(A5),{15,11;18,13},2,0),2))

这个wps生成的公式中，主要是使用了3个mid，不同的mid分别表示了出生的年，月，日。

而最外面的date函数，就表示了出生年月日。

vlookup的另一种思维



在上面D5单元格的公式：

=DATE(MID(A5,7,VLOOKUP(LEN(A5),{15,2;18,4},2,0)),MID(A5,VLOOKUP(LEN(A5),{15,9;18,11},2,0),2),MID(A5,VLOOKUP(LEN(A5),{15,11;18,13},2,0),2))

中，vlookup出现了一种用法：

VLOOKUP(LEN(A5),{15,2;18,4},2,0)

数组，

{15,2;18,4}

可以把转化为表格的形式为：

意思是，当len（A5）的长度是15的时候，取对应的2，当长度为18的时候，取数值4.

同理

VLOOKUP(LEN(A5),{15,9;18,11},2,0)

中的{15,9;18,11}转化为：

所以，通过公式就能把15位的身份证号码，以及18位的身份证号码都正确地提取出出生年月日。