用Excel从身份证号码中提取信息(年龄、性别、出生地)
用身份证号计算性别、出生日期、周岁和年龄
用身份证号计算性别、出生日期、周岁和年龄
上篇《必懂！Excel中长字符串的秘密》提到在Excel中录入长字符串的技巧，实际工作上，身份证这种长字符串运用最多，今天就来聊这个。
身份证上的信息有很多种，今天提取一些常用的特征进行说明。
1.性别：第17位为奇数是男，偶数是女。
=IF(MOD(MID(B2,17,1),2),"男","女")
2.出生日期：7~10位为出生年份，11~12位为出生月份，13~14位为出生日。
=TEXT(MID(B2,7,8),"0-00-00")
3.周岁：
=DATEDIF(D2,TODAY(),"y")
4.年龄：
=DATEDIF(D2,TODAY(),"y")&"岁
"&DATEDIF(D2,TODAY(),"ym")&"个月"
第3、4的TODAY函数也可以改为NOW函数，效果一样。
字符提取，左边用LEFT函数，中间用MID函数，右边用RIGHT 函数。
还不熟练推荐看看视频。
作者：卢子，清华畅销书作者，《Excel效率手册早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）。
在Excel中自动提取身份证中生日和性别
在Excel中自动提取身份证中生日和性别
1.转换身份证号码格式
我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式(单元格内显示的是科学记数法的格式)，需要更改一下数字格式。
选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。
在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。
2.“提取出”出生日期
=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"
/判断性别“男女”
=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")
求年龄问题,要看你的数据是哪种格式(日期、文本），举例说明：假定你的A1单元格为日期格式，内容为“1986-2-20”，在B1单元格可
输入如下公式得到年龄：
=INT((TODAY()-A1)/365)。
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表格中提取出生年月日和性别
在excel表格中提取出生年月日和性别
步骤/方法
1、转换身份证号码格式
我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。
选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。
在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。
2、“提取出”出生日期
将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例,身份证号在B2为例。
然后输入“=MID(B2,7,4)&"年"&MID(B2,1 1,2)&"月"&MID(B2,13,2)&"日"”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入.）。
回车后，你会发现在C2单元格内已经出现了该学生的出生日期。
然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。
3、判断性别“男、女”
3选中“性别”列的单元格，如D2。
输入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")”（注意如上）后回车，该生“是男还是女”已经乖乖地判断出来了。
拖动填充柄让其他学生的性别也自动输入。
这样，通过两个简单的函数，我们就可以让EXCEL从身份证号中自动提取出生日期和性别并填充到单元格内，极大地减轻了我们的输入工作量。
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(LEN(e2)=18,TEXT(MID(e2,7,8),"#年00月00日"),"19"&TEXT(MID(e2,7,6),"#年00月00日"))15/18位自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))计算年龄（新旧身份证号都可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",109-MIDB(E2,9,2),109-MIDB(E2,7 ,2)))说明：109表示当前日期是2009年，如果是2000年后出生的，要从身份证号码中计算年龄，则公式可改为=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",109-MIDB(E2,9,2),09-MIDB(E2,9, 2))) (此公式只适合18位新身份证)。
身份证号中的出生日期如何用excel批量提取出来？
身份证号中的出生日期如何用excel批量提取出来？
有了身份证号，如何快速批量把出生日期提取出来。
下面介绍两种方法：
第一种方法：用函数将身份证号中的出生日期提取出来。
单击B2单元格，在单元格中输入公式：=IF(LEN(A2)=15,DATE(MID(A2,7,2),MID(A2,9,2),MID(A2,11,2)),IF( LEN(A2)=18,DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),'号码有错'))。
说明：如果身份证号确保正确也可以用这个公式：=TEXT(IF(LEN(A2)=15,19,'')&MID(A2,7,6+IF(LEN(A2)=18,2,0)),'#-00-00')。
如果身份证都是18位，可以用这个公式：
=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))
注意：一定要将存放日期的单元格格式设置成日期类型，具体设置如下：
选中存放日期的单元格，单击右键，在弹出的菜单中选择设置单元格格式：
然后在弹出的对话框中选择数字标签，选择日期类型，单击确定。
拖动填充柄，复制公式到所有需要提出日期的单元格中，大功告成：
第二种方法：用数据菜单中的分裂命令。
将A列中的身份证号复制到B列，选中B列所有身份证号，选择数据菜单，单击分裂命令，打开分列向导：
选择固定宽度，单击下一步，在日期前后分别建立分列线，单击
下一步：
选择中间日期数据列，将列数据格式设置成日期型，单击完成，大功告成。
完成效果如下：
好累啊，给个大拇指好不好，(*^__^*) 嘻嘻……。
在EXCEL中怎样用身份证号计算年龄,性别及出生年月、查重
在EXCEL中怎样用身份证号计算年龄假设身份证号码在A1单元格 B1单元格输入公式=datedif(text(mid(a1,7,len(a2)/2-1),"0-00-00"), now(),"y") 此公式适用于15位和18位号码假设身份证号在A1单元格都是18位的在B1单元格输入公式按月份=year(today())-year(--text(mid(A1,7,8),"0-00-00"))或按年=datedif(--text(mid(A1,7,8),"0-00-00"),today(),"Y")在Excel中自动推测出生年月日及性别的技巧大家都知道，身份证号码已经包含了每个人的出生年月日及性别等方面的信息（对于老式的15位身份证而言，7-12位即个人的出生年月日，而最后一位奇数或偶数则分别表示男性或女性。
如某人的身份证号码为420400*********，它的7-12位为700101，这就表示该人是1970年元月1日出生的，身份证的最后一位为奇数1，这就表示该人为男性；对于新式的18位身份证而言，7-14位代表个人的出身年月日，而倒数第二位的奇数或偶数则分别表示男性或女性）。
根据身份证号码的这些排列规律，结合Excel的有关函数，我们就能实现利用身份证号码自动输入出生年月日及性别等信息的目的，减轻日常输入的工作量。
Excel中提供了一个名为MID的函数，其作用就是返回文本串中从指定位置开始特定数目的字符，该数目由用户指定（另有一个名为MIDB的函数，其作用与MID完全一样，不过MID仅适用于单字节文字，而MIDB函数则可用于汉字等双字节字符），利用该功能我们就能从身份证号码中分别取出个人的出生年份、月份及日期，然后再加以适当的合并处理即可得出个人的出生年月日信息。
提示：MID函数的格式为MID(text,start_num,num_chars)或MIDB(text,start_num,num_bytes)，其中Text是包含要提取字符的文本串；Start_num是文本中要提取的第一个字符的位置（文本中第一个字符的start_num为1，第二个为2……以此类推）；至于Num_chars则是指定希望MID从文本中返回字符的个数。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
用Excel从身份证号码中提取信息
（年龄、性别、出生地）
出生年月日信息提取：
方法一：在记录列中输入公式：=--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文件，另存为××文件，即可使用。
谢谢你的使用。
水晶六彩。