Excel从身份证号中提取出生年月和年龄讲解

合集下载

excell-----使用帮助,如何从中身份证中提取出生年月,性别,年龄等

Excel中如何根据身份证号自动求出出生年月？假如身份证号数据在A1单元格，在B1单元格中编辑公式=IF(LEN(A1)=15,"19"&MID(A1,7,2)&MID(A1 ,9,2)&MID(A1,11,2),MID(A1,7,4)&MID(A1,11 ,2)&MID(A1,13,2))这样输出格式就都是19821010这种格式了。

Excel中如何根据身份证号自动求出性别？假定身份证号在A1单元格，则在B1单元格中编辑公式=IF(AND(LEN(A1)=15,MOD(MID(A1,15,1)*1 ,2)=1),"男",IF(AND(LEN(A1)=18,MOD(MID(A1,17,1)*1 ,2)=1),"男","女"))Excel中如何根据身份证号自动填充年龄？假设A列从A1开始是身份证号，在B列中进行转换，则在B1中输入=YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID (A1,7,2),MID(A1,7,4))，然后再向下复制即可。

公式解析：一、分析身份证号码身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。

15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。

18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。

例如，某人的身份证号码(15位)是320521*********，那么表示1972年8月7日出生，性别为女。

如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。

用Excel从身份证号码中提取信息(年龄、性别、出生地)

用Excel从身份证号码中提取信息（年龄、性别、出生地）1.出生年月日信息提取：方法一：在记录列中输入公式：=--TEXT(MID(B2,7,6+IF(LEN(B2)=15,0,2)),"#-00-00")，往下复制，无论15位还是18位身份证号码全部搞定，方法最简单。

方法二：在记录列中输入公式：=--IF(LEN(B2)=15,TEXT(MID(B2,7,6),"##-00-00"),TEXT(MID(B2,7,8),"####-00-00"))，往下复制，无论15位还是18位身份证号码全部搞定，公式增加了几个字符，原理差不多，结果一致。

原理：使用函数text、if、mid、len。

注意：1、B列存放身份证号码。

存放在其它列，则在公式中作相应调整。

2、计算出错（#V ALUE!），说明身份证号码有错。

3、日期显示格式，可在单元格格式中设置。

性别信息提取：在记录列中输入公式：=IF(LEN(B2)=15,IF(MOD(RIGHT(B2),2)=0,"女","男"),IF(MOD(LEFT(RIGHT(B2,2)),2)=0,"女","男"))无论15位还是18位身份证号码全部轻松完成。

原理：使用函数IF、LEN、MOD、LEFT、RIGHT。

注意：1、B列存放身份证号码。

存放在其它列，则在公式中作相应调整。

2、计算出错（#V ALUE!），说明身份证号码有错。

出生地信息提取：在记录列中输入公式：=LEFT(B2,6)，往下复制，然后根据代码用VLOOKUP查询发证地或者是出生地信息。

Excel文件模板：从身份证号码中提取信息使用的模板：使用Excel从身份证号码提取信息.xls点击该图标，打开该EXCEL文件，另存为××文件，即可使用。

谢谢你的使用。

EXCEL批量提取身份证号码里的出生日期,三种方法大PK,你喜欢哪种

EXCEL批量提取身份证号码里的出生日期，三种方法大PK，你喜欢哪种在实际工作中，经常会从身份证号码里提取出出生日期信息，下面我们来详解三种方法快速提取身份证号码里的出生日期，至于你喜欢哪种？看完下面的介绍，你再决定用哪种！方法一：快速填充法A列输入身份证号码，B列第一个单元格内手动输入第一个身份证号码里的出生日期（用斜杠日期格式），身份证号码里的出生日期就是从第7位开始，连续8个数字代表出生的年月日。

选中第一个手工输入了出生日期的单元格和下方空白单元格，在键盘上按下“CTRL+E”，快速填充组合键。

选中单元格批量提取出A列单元格里的出生日期，但CTRL+E是根据手动录入的数据发现规律提取出数据，但偶尔也会犯错误，提取的数据不一定完全正确，如下图，前面三个单元格的身份证出生日期的日期都是12结尾，CTRL+E提取数据的时候日期规律就没有把握对。

面对这种情况，我们可以稍微的调换下A列的身份证号码，第二个单元格里的身份证号码日期尽量不要和第一个单元格身份号码里的日期相同，手工在B列输入第二个日期，按下回车，系统默认的是短日期，小于10的月份或日期会自动省略前面的0。

如果现在按下CTRL+E，肯定也不能提取出数据。

选中B列数据，在键盘上按下“C TRL+1”打开单元格格式设置窗口，在自定义里修改日期格式为“YYYY/MM/DD”。

点击确定，B列设置好日期格式后，选中B列里添了日期的单元格和下方的空白单元格，按下CTRL+E，批量提取出A列身份证号码里的日期，核对下，提取正确。

数据分列法选中A列的身份证号码，点击菜单栏上“数据-分列”。

弹出分列向导窗口，勾选“固定宽度”。

在第二步里，在出生日期的前后添加分隔线。

在第三步里，选中下方预览框里第一列和最后一列，勾选“不导入此列”，选中第二列，勾选“日期”，后面选择日期格式YMD或其他，点击目标区域，选择日期放置的单元格。

点击确定，A列身份证号码里的出生日期提取出来，选中B列的日期格式，可调整为其他日期格式。

在EXCEL中根据身份证号计算出生年月日、年龄

在EXCEL中根据身份证号计算年龄、出生年月、性别在EXCEL中如何根据身份证号计算年龄、出生年月、性别呢？下面来介绍一下，希望对您有帮助1.根据身份证号计算年龄=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(L EN(A2)=18)*2),"#-00-00"),TODAY(),"y"),)注意：A2单元格为身份证号，且为文本格式。

Excel如何从身份证号中提取出生年月日[详细版]

Excel如何从身份证号中提取出生年月日[详细版]在使用excel的时候，经常会遇到从身份证号中提取出生年月日这样的问题，今天小编就带大家一起来从身份证号中提取出生年月日，我们首先需要理解从身份证中提取出生年月日的原理，我们的身份证是一个18位的数字，其中第7位到第8位就代表着我们的出生年月日，以下小编介绍的三种方法都是以此为基础。

工具/原料•电脑一台•方法一.1.这里小编在B列有三个身份证号码，需要在C列中让excel自动填充出生年月日，这里小编先进行介绍方法一，方法一主要使用的是MID函数，如下图：...2.在C列对应的单元格中我们输入公式“=MID(B3,7,4)&"年"&MID(B3,11,2)&"月"&MID(B3,13,2)&"日"”，其中MID函数作用为从指定的字符串中截取出指定数量字符，例如公式中的“MID(B3,7,4)”,第一个参数表示B3单元格中的身份证号，7表示从第7位开始计算，4表示从第7位开始共截取几位数字，其中公式中的“&”表示拼接字符串，如下图：...3.回车之后我们得到使用MID函数和&拼接符自动计算得出的出生年月日，如下图：...4.然后我们将公式填充到其他单元格，得到所有身份证的出生年月，如下图：..END.方法二.1.这里小编介绍方法二，主要使用的是MID函数和TEXT函数，如下图：...2.我们在对应的单元格输入“=TEXT(MID(B3,7,8),"0000-00-00")”，其中MID函数作用为从指定的字符串中截取出指定数量字符，TEXT函数作用为将制定的字符转化为自己想要的格式文本，如下图：...3.回车之后，我们就可以看到我们想要的格式出生年月，如下图：...4.然后将公式填充剩下的单元格，我们就得到了全部的出生年月，如下图：.END.方法三.1.这里小编介绍方法三，这个方法中小编主要使用的是分列的方法，如下图：..2.首先我们将身份证这一列全部选中，然后点击菜单“数据”，然后点击选项“分列”如下图：...3.然后我们在分列向导中选择“固定宽度”，如下图：...4.由于出生年月为第7到第14位，所以这里我们在第7位之前加分割线，在第14位之后加分割线，如下图：...5.然后选中第7位之前区域，然后点击“不导入此列”，如下图：...6.然后选中第14位之后区域，然后点击“不导入此列”，如下图：...7.然后选中第7列与第14列中间的区域，然后选择“日期”，然后再目标区域中填写“$C$3”表示分列之后的数据填充到哪一列，如下图：...8.点击完成之后我们得到所有身份证分列之后的出生年月日，如下图：.. .。

EXCEL电子表格用函数计算年龄、工龄及从身份证中算出周岁等技巧

电子表格常用函数汇总―――（潘世华2013年版）注：(1)如何截取身份证号第17位：MID(C2,17,1)Value（字符型数字）这个函数就是转换字符型数字转成数字N(value)这个函数,将不是数值形式的值转成数值形式.日期转换成序列值,True转换成1,False转换成0不需要函数，乘1即可例如001 变数值=A1*1 即等于11、用“身份证号”提起出生年月日第一种公式：=IF(LEN(C2)=15,19&MID(C2,7,2)&"/"&MID(C2,9,2)&"/"&MID(C2,11,2 ),IF(LEN(C2)=18,MID(C2,7,4)&"/"&MID(C2,11,2)&"/"&MID(C2,13,2) ,""))说明：C2为身份证号码所在的单元格，在实践过程中，把“C2”转换成实际表中的“身份证栏”（身份证栏的输入格式为“文本”）。

2、用“身份证号”提起出生年月日第二种公式：（很好）=CONCATENATE(MID(C2,7,4),"年",MID(C2,11,2),"月",MID(C2,13,2),"日")3、“用身份证”号算出性别第一种公式：=IF(LEN(C2)=15,IF(OR(RIGHT(C2,1)="0",RIGHT(C2,1)="2",RIGHT(C2 ,1)="4",RIGHT(C2,1)="6",RIGHT(C2,1)="8"),"女","男"),IF(LEN(C2)=18,IF(OR(MID(C2,17,1)="0",MID(C2,17,1)="2",MID( C2,17,1)="4",MID(C2,17,1)="6",MID(C2,17,1)="8"),"女","男"),""))说明：C2为身份证号码所在的单元格，在实践过程中，把“C2”转换成实际表中的“身份证栏”（身份证栏的输入格式为“文本”）。

电子表格中身份证号提取生日和年龄汇总

电子表格中身份证号提取生日和年龄汇总注：（以下公式假设身份证在A1列）1、身份证在excel里面输入的时候，18位的只能以文本方式输入或者前加‘单引号，如果是文本怎么提取？如果有’等其他符号如何提取？如果只提取年份yyyy 和只提取月日mm-dd怎么提取？a、如果是文本则输入此公式：=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"00-00 -00")b、如果是前加’附号的，那就试试这个公式：=TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2),"00-00-00")c、如果只提取年份：=YEAR(TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2) ,"00-00-00"))d、如果只提取月日：=TEXT(TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2) ,"00-00-00"),"mm-dd")补充：如果身份证输入时采用了特殊格式如510182000000000000自动填入，则将上述第二个公式改一下：=TEXT((LEN(A1)<=16)*19&MID(A1,3,6+(LEN(A1)>=18)*2),"00-00-00")-----------------------------------------------------2、假设你的生日是此格式*年*月*日，要修改为如下格式，则：a、如需修改为**.**的表达式如下：=CONCATENATE(TEXT(A1,"yyyy"),".",TEXT(A1,"mm"))b、如需修改为**.**.**的表达式如下：=CONCATENATE(TEXT(A1,"yyyy"),".",TEXT(A1,"mm"),".",TEXT (A1,"dd"))c、如需修改为**-**-**的表达式如下：=CONCATENATE(TEXT(A1,"yyyy"),"-",TEXT(A1,"mm"),"-",TEXT (A1,"dd"))------------------------------------------------------3、身份证号或日期提取年龄a、直接从身份证提取当年的年龄：=2012-MID(A1,7,4)b、假设你的日期格式为1970-3-27，则输入如下公式转化为你当天的年龄：=INT（（TODAY()-A1)/365）。

Excel表中身份证号码提取出生年月

Excel表中身份证号码提取出生年月,性别,年龄的使用技巧1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。

18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。

从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。

MI D()——从指定位置开始提取指定个数的字符（从左向右）。

对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。

综合上述分析，可以通过下述操作，假如身份证号数据在A1单元格，在B1单元格中编辑公式，完成形如1978-12-24样式的出生年月日自动提取：=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7, 4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2))回车确认即可。

如果只要“年-月”格式，公式可以修改为=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2))3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，（说明：公式中的B2是身份证号）=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=1 8,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错"))根据身份证号码求年龄：=IF(LEN(B2)=15,2012-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2012-VALUE(MID (B2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;另一方法：这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式。

Excel：根据身份证号码提取性别、年龄、出生年月日,建议收藏

Excel：根据身份证号码提取性别、年龄、出生年月日，建议收藏
Excel:根据身份证号码提取性别、年龄、出生年月日,建议收藏性别打开Excel,在表格中输入身份证信息,如图在二代身份证中的第17位是性别信息,奇数为男,偶数为女。

在【G2单元格】输入【=IF(MOD(MID(B2,17,1),2)=1,”男”,”女”)】
输入完成后按【回车Enter】,即可得到结果然后选中【G2】单元格,并把鼠标放置到G2单元格的右下角,当鼠标变成如图【粗加号】时,一直按【左键】向【下拉】,即可得到全部的性别信息
出生年月日、年龄二代身份证号的第7~14位数字表示:出生年、月、日。

在【C2单元格】中输入【MID(B2,7,4) 】
输入完成后按【回车Enter】,即可得到【出生年】在【D2单元格】中输入【MID(B2,11,2) 】输入完成后按【回车Enter】,即可得到【月】在【E2单元格】中输入【=MID(B2,13,2) 】输入完成后按【回车Enter】,即可得到【日】如果需要计算年龄,可以使用当年的年份（例如2018）减去提取出来的年,即在【F2单元格】中输入【=2018-MID（B2,7,4）】
输入完成后按【回车Enter】,即可得到【年龄】。

Excel表格中根据身份证号码自动填出生日期、计算年龄

Excel表格中根据身份证号码自动填出生日期、计算年龄18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))),2)=0,"女","男")15/18位都可以的公式：转换出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TEXT(MID(e2,7,6),"#-0 0-00"))自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男")) 计算年龄（新旧身份证号都可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2,9,2),107-MIDB(E2,7 ,2)))WPS表格提取身份证详细信息前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。

而我们单位员工人数众多，短时间内统计相关信息并且输入计算机几乎是不太可能的。

幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。

所以，干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。

身份证号放在A2单元格以下的区域。

我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。

由于现在使用的身份证有15位和18位两种。

所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别不同情况进行相关处理。

关于身份证信息提取的Excel函数公式

Excel函数公式计算技巧
一、提取性别：
以D2列【身份证号码】栏为坐标，
则C2列【性别】栏输入公式：=IF(MOD(MID(D2,17,1),2)=1,"男","女")
二、提取出生日期：
以D2列【身份证号码】为坐标，
则E2列【出生日期】栏公式为：=MID(D2,7,8)
三、提取年龄：
以D2列【身份证号码】为坐标，
则F2列【年龄】栏公式为：=YEAR(TODAY())-MID(D2,7,4)
四、提取生肖：
以D2列【身份证号码】为坐标，
则G2列【生肖】栏公式为：=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(D2,7,4),12)+1,1)
五、计算工龄：
以H2列【入职时间】栏为坐标，
则I2列【工龄】栏输入公式：=DATEDIF(H2,TODAY(),"Y")
六、计算工龄工资：
以I2 列【工龄】为坐标，若工龄工资每年加50元。

则L2列【工龄工资】栏公式为：则=I2*50
七、计算合同到期日：
以H2列【入职时间】和J2【合同期限】为坐标
则F2列【合同到期日】栏公式为：=DATE(YEAR(H2)+J2,MONTH(H2),DAY(H2)-1)。

从证件号码中提取年龄性别出生日期等

从证件号码中提取年龄性别出生日期等在EXCEL中如何根据身份证号计算年龄、出生年月、性别呢？下面来介绍一下，希望对您有帮助1.根据身份证号计算年龄=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2) =18)*2),"#-00-00"),TODAY(),"y"),)注意：A2单元格为身份证号，且为文本格式。

2.根据身份证号计算出生年月日第一种，计算出来的格式为××年××月××日=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月"&MID(A2,11,2)&"日",MID(A2,7,4)&"年"&MID(A2,11,2)&"月"&MID(A2,13,2)&"日")第二种，计算出来的格式为××年××月=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月",MID(A2,7,4)&"年"&MID(A2,11,2)&"月")3.根据身份证号计算性别=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,"男","女")4.在残疾证号码中提取残疾等级和类别残疾等级：=TEXT(--RIGHT(A1,1),"[dbnum1]")&"级"残疾类别：=LOOKUP(--MID(A1,19,1),{1,2,3,4,5,6,7},{"视力","听力","言语","肢体","智力","精神","多重"})或=IF(A1="","",CHOOSE(--MID(A1,19,1),"视力","听力","言语","肢体","智力","精神","多重"))合并：=LOOKUP(--MID(A1,19,1),{1,2,3,4,5,6,7},{"听力","视力","言语","肢体","智力","精神","多重"})&TEXT(--RIGHT(A1,1),"[dbnum1]")&"级"5.在残疾证号码中提取身份证号码=left(残疾证号码位置,18)6.EXCEL如何在一列中查找重复的数据选中A列，格式--条件格式---公式，输入：=countif(a:a,a1)>1然后点格式，自己选红色即可，确定。

excel身份证号码提取年龄

excel身份证号码提取年龄Excel身份证号码提取年龄作为一款广泛应用于数据处理和分析的工具，Excel在日常工作中扮演着重要的角色。

在某些情况下，我们可能需要从身份证号码中提取出个人的年龄信息。

本文将介绍使用Excel函数来实现这一功能。

身份证号码通常包含了个人的出生年月日信息，通过解析身份证号码，我们可以计算出个人的年龄。

在Excel中，我们可以利用一些内置的日期和文本函数来完成这一任务。

首先，我们需要明确身份证号码中的各个部分所代表的含义。

通常，身份证号的前六位表示户籍所在地，接着的八位表示出生年月日，再接下来的四位表示顺序码，最后一位是验证位。

通过解析身份证号码，我们可以使用MID函数来提取出生年月日。

MID函数用于从文本字符串中截取指定位置的字符，我们可以指定起始位置和截取的长度。

要提取出身份证号码中的出生日期，我们可以使用如下的公式：=DATEVALUE(MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2))。

在这个公式中，A2表示存储了身份证号码的单元格，在实际运用时，需要根据具体的情况进行调整。

通过这个公式，我们可以将身份证号码中的年、月、日提取出来，并以日期的格式显示出来。

现在，我们需要计算出个人的年龄。

为了计算年龄，我们可以使用TODAY函数来获取当前日期，再利用DATEDIF函数来计算出生日期和当前日期之间的天数差。

通过如下的公式可以计算出年龄：=DATEDIF(B2,TODAY(),"y")。

在这个公式中，B2表示存储了出生日期的单元格，在实际运用时，需要根据具体的情况进行调整。

使用这个公式，我们可以得到一个人的年龄。

但需要注意的是，这个公式仅仅计算了整年的差距，不考虑具体的天数差。

如果需要精确到天数，我们可以调整公式为：=DATEDIF(B2,TODAY(),"yd")。

Excel表格中身份证号码提取出生年月、性别、年龄等的使用技巧.pdf

Excel表中身份证号码提取出生年月、性别、年龄的使用技巧方法一：1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。

18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。

从身份证号码中提取出表示出生年、月、日的数字，用文本函数MI D()可以达到目的。

MID()——从指定位置开始提取指定个数的字符（从左向右）。

对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF ()和字符个数计算函数LEN()辅助使用可以完成。

综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取：假如身份证号数据在A1单元格，在B1单元格中编辑公式=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2))回车确认即可。

如果只要“年-月”格式，公式可以修改为=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2))3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考：说明：公式中的B2是身份证号根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女", "男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0, "女","男"),"身份证错"))根据身份证号码求年龄：=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;方法二：这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：说明：公式中的B2是身份证号1、根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女", "男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0, "女","男"),"身份证错"))2、根据身份证号码求出生年月：=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2)),"身份证错"))3、根据身份证号码求年龄：=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))早期的身份证号码为15位数字，现在使用的身份证号码为18位数字，它们的含义如下：（1）15位：1-6位为地区代码，7-8位为出生年份（2位），9-10位为出生月份，11-12位为出生日期，第13-15位为顺序号，这3位奇数为男，偶数为女。

身份证号码提出生年月性别年龄方法公式

一、提取出生日期如果我们要从一个人的身份证号码中批量提取其出生年月日，并表示成“yyyy-mm-dd”形式，可以这样做，假设身份证号码在C列，在D列中输入公式=IF(LEN(C6)=15,"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2),MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2))这个公式的含义就是，当其检查到C6单元格中的数据是15位的时，就显示"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2)的计算结果，否则就显示MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2)的计算结果。

如：若C6单元格中是441000*********，在D6单元格中计算出的结果是“1950-01-12”；若C6单元格中是410005************，在D6单元格中计算出的结果是“1945-11-16”。

二、提取性别在E6单元格输入公式=IF(LEN(C6)=15,IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),"女","男"),IF(MID(C6,17,1)/2=INT(MID(这个公式的含义是如果C6单元格是一个15位数，就显示IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),"女","男")的计算结果；否则，显示IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),"女","男"))的计算结果。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

假设数据在A1单元格B1单元格输入公式=right(a1,len(a1)-2) (公式中的“2”是指栓出前面的位数）或=substitute(a2,"SF",)双击填充柄填充或下拉填充然后复制B列,选择性粘贴为数值,替换到A列中如何利用Excel从身份证号中提取出生年月和年龄等信息？2007年12月16日 07:41 P.M.一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。

15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。

18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。

例如，某员工的身份证号码(15位)是320521*********，那么表示1972年8月7日出生，性别为女。

二、提取个人信息这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。

如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。

图1 输入身份证号码1．提取出生年月信息由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。

在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。

MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。

MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。

IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。

如果需要显示为“70年12月”这样的格式，请使用DATE 格式，并在“单元格格式→日期”中进行设置。

2．提取性别信息由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错。

例如性别信息统一在B列填写，可以在B2单元格中输入公式=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女") 其中：LEN(C2)=15：检查身份证号码的长度是否是15位。

MID(C2,15,1)：如果身份证号码的长度是15位，那么提取第15位的数字。

MID(C2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。

MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。

IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。

回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。

如图2所示，现在这份报表无论是提取信息或是核对，都方便多了！查看文章Excel表中身份证号码提取出生年月,性别,年龄的使用技巧2007年12月24日星期一 12:171.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。

18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。

从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。

MID()——从指定位置开始提取指定个数的字符（从左向右）。

对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。

综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取：假如身份证号数据在A1单元格，在B1单元格中编辑公式=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11 ,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2))回车确认即可。

如果只要“年-月”格式，公式可以修改为=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2))3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考：说明：公式中的B2是身份证号根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错"))根据身份证号码求年龄：=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,200 7-VALUE(MID(B2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;另一方法：这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：说明：公式中的B2是身份证号1、根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错"))2、根据身份证号码求出生年月：=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11 ,2)),"身份证错"))3、根据身份证号码求年龄：=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LE N(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))提取身份证号码的每一位，类推：第一位 =IF(LEN(C2)=15,MID(C2,1,1),MID(C2,1,1))第二位 =IF(LEN(C2)=15,MID(C2,2,1),MID(C2,2,1))第三位 =IF(LEN(C2)=15,MID(C2,3,1),MID(C2,3,1))Excel身份证号码提取的出生年月日格式为： 1969年12月24日="19"&MID(C2,9,2)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日"格式为： 196912="19"&MID(C2,9,2)&""&MID(C2,11,2)格式为： 1969.12="19"&MID(C2,9,2)&"."&MID(C2,11,2)将光标移至需要输入日期的单元格中，然后输入“="19"&MID(身份证单元格,9,2)&"年"&MID(身份证单元格,11,2)&"月"&MID(身份证单元格,13,2)&"日"”内容。

其中MID(B3,9,2)就是从身份证号码的第9位开始取2位数，得出该员工的出生年份，MID(B3,11,2)就是得出该员工的出生月份，而MID(B3,13,2)则是该员工的出生日期，这些信息再加上年、月、日等文字就会组成该员工的准确出生年月日“1970年1月1日”。

EXCEL根据身份证号码提取性别、出生年月、求年龄EXCEL根据身份证号码自动提取性别和出生年月及求年龄注：1.身份证号码前6位是北京市北京县的号码，后面的数字是随输入的，如有雷同，纯属巧合，敬请见谅。

2.所有求出的信息都是直接从身份证号码里取得的。

3.退休日期按男60女55周岁计算4.有效性验证已直接放到A列的数据有效里，单独列出是为了方便查看5.年龄按周岁计算。

公式，直接粘在单元格上，假如A列是身份证号码，B列是以下任意一个（A2行为身份证号码，B2为下列任一，类推）：出生日期=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)* 2),"#-00-00")+0,)性别 =IF(A2<>"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),)年龄=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN( A2)=18)*2),"#-00-00"),TODAY(),"y"),)全部改为18位=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(S UM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)全部改为15位=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))退休日期=IF(A2<>"",EDATE(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2 )=18)*2),"#-00-00"),660+MOD(RIGHT(LEFT(A2,17)),2)*60),) 号码有效性验证=OR(LEN(A2)=15,IF(LEN(A2)=18,MID("10X98765432",MOD(SUM(M ID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17" )))),11)+1,1)=RIGHT(A2)))所属省份 =IF(A2<>"",VLOOKUP(LEFT(A2,2),data,2,),)所属地区 =IF(A2<>"",VLOOKUP(LEFT(A2,6),data,2,))。