身份证号里的信息提取

合集下载

最新 第二代身份证号码提取出生年月日的公式 (很简单)

最新 第二代身份证号码提取出生年月日的公式 (很简单)

1、第二代身份证号码提取出生年月日的公式:=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)注:A是列,1是行,&在数字键7的上方。

2、第二代身份证号码提取性别男、女的公式:=IF(MOD(MID(A1,17,1),2),"男","女")注:A是列,1是行。

3、excel用身份证号算年龄的工式比如身份证号保存在a1单元格,那么可以使用=mid(a1,7,4)来取出出生年,再用当前年份减去这个就是年龄了。

4、提取身份证出生年月="19"&MID(B1,9,2)&"年"&MID(B1,11,2)&"月"&MID(B1,13,2)&"日"5、提取身份证年龄=YEAR(NOW())-YEAR(IF(LEN(B1)=18,DATE(MID(B1,7,4),MID(B1,11,2),M ID(B1,13,2)),DATE(MID(B1,7,2),MID(B1,9,2),MID(B1,11,2))))6、提取身份证性别=IF(LEN(B1)=15,IF(MOD(MID(B1,15,1),2)=1,"男","女"),IF(MOD(MID(B1,17,1),2)=1,"男","女"))7、公式内的“B1”代表的是输入身份证号码的单元格。

Excel自动提取身份证中生日和性别以及年龄(注意:以B2单元格中是18位身份证号为例)一、提取出生日期的输入公式=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"二、判断性别“男女”的输入公式=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")三、利用身份证号码求年龄=IF(B2="","",DATEDIF(TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN (B2)=18)*2),"#-00-00"),TODAY(),"y"))八、计算年龄的公式?datedif(开始日期,结束日期,"M")九、用公式计算最大年龄?max(D3:D21)十、用公式计算25--35岁之间的人数?=COUNTIFS(D3:D21,">"&25,D3:D21,"<"&35)十一、计算25岁以下的人数?=COUNTIF(D3:D21,">"&25)十二、计算25岁以下人数的比例?=COUNTIF(D3:D21,">"&25)/count(D3:D21)十三、用excel做表输入年月日,可以变成例如1986-01-01的格式吗?能的话怎么变?先选中单元格,鼠标右击,“设置单元格格式”,“数字”,“自定义”,在类型处输入e-mm-dd 确定十四、1.自动录入性别:“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”if假如(len(C2)得到C2的字符长度等于15,[15时mod除以2的余数(取mid(C2单元格,第15个的,1个字符),除以2)],[不等于15条件不成立时if(mod后面除以2的余数(取C2,第17个,的1个字符),除以2)]所涉及函数:len() 取单元格字符长度。

如何从身份证号中提取完整的出生年月日

如何从身份证号中提取完整的出生年月日

如何从身份证号中提取完整的出生年月日第一篇:如何从身份证号中提取完整的出生年月日如何从身份证号中提取完整的出生年月日?公式:=CONCATENATE(MID(B3,7,4),“年”,MID(B3,11,2),“月”,MID(B3, 13,2),“日”)备注:B3为身份证号所在单元格。

第二篇:身份证号中提取出生年月日公式从身份证中提取出生年月日的公式=text(mid(A1,7,8),“0-00-00”)= text(mid(A1,7,8),“#年00月00日”)第三篇:Excel如何从身份证号中提取出生年月日[详细版][范文] Excel如何从身份证号中提取出生年月日[详细版] 分步阅读Excel表中经常要录入大量的数据,懂得其中的一些技巧,往往可以节省大量的时间和精力,大幅度提高工作效率,今天就以身份证码号提取出出生日期为例,带大家认识一下mid函数。

方法/步骤1.1 首先,打开工作表,如图(身份证号为随便录入),现在我们要清楚要提取的身份证号所在列为B,第一个身份证码坐标为B2,将光标点击在想要提取为日期的单元格,即C2,当然你也可点在别的单元格。

2.2 将光标点击C2,将函数=MID(B2,7,8)输入如图所在位置,回车或是点击任一单元格,即自动提取出出生日期。

B2为身份证号的坐标,7为从第7位数字提了起,8为提取8个连续数字。

如只想提取出年月,可以输入=MID(B2,7,6)3.3 将光标放在单元格右下角,光标变为“+”,按住向下拖动单元格,放开鼠标左键即全部自动提取出各身份证号中的出生日期。

4.4 如果想为出生日期加上“年月日”,可以用连字符“&”来完成,分段提取出年月日来实现,如图格式,文字属于字符串,因而要加上双引号。

5.5 同样将光标放在单元格右下角,光标变为“+”,按住向下拖动单元格,放开鼠标左键即全部自动提取出各身份证号中的出生日期,而此时将同时出现“年月日”等字样.6.6 如果想要按样这样的格式“XXXX-XX-XX”,可以将函数中的年月日都改成“-”即可,如图。

从身份证号中提取生日、年龄、性别、归属地和属相

从身份证号中提取生日、年龄、性别、归属地和属相

从⾝份证号中提取⽣⽇、年龄、性别、归属地和属相我们的⾝份证号码中隐藏着很多信息,有时候我们为⼯作需要要从⾝份证号码中提取信息,如出⽣⽇期、年龄、性别、归属地和属相等,今天我们就讲⼀下提取这⼏种信息的⽅法;1.提取出⽣⽇期从⾝份证号码中提取出⽣⽇期的⽅法有很多种,今天我们介绍其中的⼏种;1.1)快速填充法如果⾝份证号码都是18位的或者15位的,则可以直接⽤快速填充,例如下图:A2:A6为⾝份证号码,在B2单元格中输⼊第⼀个号码的中间8位,按Ctrl+E(快速填充),或者下拉填充,点击右下⾓图标,选择快速填充;然后【数据】【分列】【下⼀步】【下⼀步】,选择【⽇期】【完成】;1.2)分列法选中⾝份证号码所在单元格,【数据】【分列】选择【固定宽度】【下⼀步】,选取宽度,刚好把⽇期选择出来,【下⼀步】,这样就把⾝份证号码分成三列,前六位所在列和后四位所在列选择【不导⼊此列】,出⽣⽇期所在列选择【⽇期】,【⽬标区域】选择C2:C6,【完成】1.3)以上两种⽅法适合只有18位的⾝份证号或者只有15位的⾝份证号,即有18位也有15位的,可以⽤函数法;在D2单元格输⼊=IF(LEN(A2)=18,TEXT(MID(A2,7,8),"0-00-00"),TEXT(MID(A2,7,6),"1900-00-00")),向下填充;如果只有18位⾝份证号则输⼊=TEXT(MID(A2,7,8),"0-00-00")即可;⽤MID函数从第7位数开始提取8位数,⽤TEXT函数转换成⽇期格式;⽇期有两种标准格式:1982/9/1或者1982-09-01;1.4)⽤DATE函数DATE函数的⽤法:DATE(年,⽉,⽇),在单元格输⼊=DATE(1982,9,1),结果得到1982/9/1;在E2单元格输⼊=IF(LEN(A2)=18,DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),DATE(MID(A2,7,2),MID(A2,9,2),MID(A2,11,2))),向下填充;如果只有18位的⾝份证号,则输⼊=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))即可;⽤MID函数从第7位数开始提取4位数,从第11位数开始提取2位数,从第13数位开始提取2位数,分别做为DATE函数的年、⽉、⽇;2.从⾝份证号码中提取年龄在F2单元格输⼊=IF(LEN(A2)=18,DATEDIF(TEXT(MID(A2,7,8),"00-00-00"),TODAY(),"Y"),DATEDIF(TEXT(MID(A2,7,6),"1900-00-00"),TODAY(),"Y")),向下填充;如果只有18位⾝份证号码,则输⼊=DATEDIF(TEXT(MID(A2,7,8),"00-00-00"),TODAY(),"Y")即可,把⽇期转换成标准⽇期,⽤DATEDIF函数计算出⽣⽇期到今天有多少年,即为年龄;3.从⾝份证号码提取性别18位⾝份证号码中,第17位数为奇数(1,3,5,7,9),是男性,第17位数为偶数(0,2,4,6,8),是⼥性;15位数最后⼀位数为奇数(1,3,5,7,9),是男性,最后⼀位数为偶数(0,2,4,6,8),是⼥性;在G2单元格中输⼊=IF(LEN(A2)=18,IF(MOD(MID(A2,16,2),2),"男","⼥"),IF(MOD(MID(A2,14,2),2),"男","⼥")),向下填充;如果只有18位⾝份证号,则输⼊=IF(MOD(MID(A2,16,2),2),"男","⼥")4.从⾝份证号码中提取归属地在H2单元格中输⼊=VLOOKUP(--LEFT(A2,6),⾝份证前六位所在地区对照表!B:C,2,),向下填充;⽤LEFT函数提取⾝份证号前六位,提取出来的六位数是⽂本形式,⽤双负号(--)转换成数值,然后根据《⾝份证前六位所在地区对照表》查找出对应的归属地;对照表可在百度搜到;5.从⾝份证号中提取属相在I2单元格中输⼊=IF(LEN(A2)=15,MID("⿏⽜虎兔龙蛇马⽺猴鸡狗猪",MOD("19"&MID(A2,7,2)-4,12)+1,1),MID("⿏⽜虎兔龙蛇马⽺猴鸡狗猪",MOD(MID(A2,7,4)-4,12)+1,1)),向下填充;如果只有18位⾝份证号,则输⼊=MID("⿏⽜虎兔龙蛇马⽺猴鸡狗猪",MOD(MID(A2,7,4)-4,12)+1,1)即可;。

提取身份证信息方法

提取身份证信息方法

提取身份证信息方法一、提取出生年月日设A1=身份证号码,B1输出结果15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。

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

输出公式:(格式:0000-00-00)=TEXT(MID(A1,7,8),"#-00-00")输出公式:(格式:00000000)公式如下:=IF(LEN(A1)=15,“19”&MID(A1,7,6),MID(A1,7,8))二、“年-月-日”格式出生日期转为年龄设A1=出生日期,B1输出结果如果出生日期如:A1=1980-11-25,那公式如下方式一:=DATEDIF(A1,TODAY(),"y")方式二:=YEAR(TODAY())-YEAR(A1)三、“年月日格式出生日期转为年龄设A1=出生日期,B1输出结果如果出生日期如:A1=19801125,那公式如下=DATEDIF(TEXT(A1,"0000-00-00"),TODAY(),"y")四、把yyyymmdd改成yyyy-mm-dd格式方法如下:1、选中要更改格式这一栏,然后执行数据--分列。

2、在弹出的窗口中选中(分隔符号)这一栏。

3、然后点击下一部,在弹出的窗口中选中(tab键)。

再点击下一步。

4、这一步很关键(在列数据格式中选中“日期YMD”格式)。

然后点击完成。

5、这时在表格出现的格式是YYYY-M-D,然后再选中日期这一栏,执行自定义格式yyyy -mm-dd。

五、提取身份证号前6位,再加三个“0”做为户籍代码=left(a1,6) & ”000”六、从身份证前6位号提出户籍信息假设身份证号在sheet1表,身份证号从A2开始。

从身份证号码提取年龄性别籍贯信息公式

从身份证号码提取年龄性别籍贯信息公式

姓名身份证号码提取性别提取出生日期提取现在年龄提取生肖(从出身日期中提取)身份证号码是否正确提取所属省份(从地址码提取)提取所属地区(从地址码提取)张三110221*********女1965/8/1556蛇TRUE北京市(市辖区)昌平李四510221************男1974/12/147虎TRUE四川省重庆市王五132426*********男1959/6/2062猪TRUE河北省保定地区完县周六140121*********女1970/2/2851狗TRUE山西省太原市清徐县钱七350583************男1978/10/1243马TRUE福建省泉州市南安市彭九511801*********女1952/9/2569龙TRUE四川省雅安市市辖区刘二620123*********女1979/5/1342羊TRUE甘肃省兰州市榆中县说明如下:以B2单元格为例提取生日1965/8/15此为公式中第1种方法的结果公式1=DATE(MID(B2, 7,IF(LEN(B2)= 18,4,2)),MID( B2,IF(LEN(B2) =18,11,9),2), MID(B2,IF(LEN (B2)=18,13,11 ),2))2=--TEXT(MID(B2,7 ,6+(LEN(B2)=1 8)*2),"0-00-00")3=TEXT(RIGHT(T EXT((0&MID(B2 ,7,11))-500,"1900-00-00,;!0"),10), "e-mm-dd;;;错误")4=TEXT(TEXT((0 &MID(B2,7,11) )-500,"00-00-00,;!0"),"e-mm-dd;;;错误")5=TEXT(MID(REP LACE(B2,7,,IF (LEN(B2)=15,1 9,)),7,8),"00 00-00-00")6=TEXT(REPLACE (MID(B2,7,6+( LEN(B2)=18)*2 ),1,,IF(LEN(M ID(B2,7,6+(LE N(B2)=18)*2)) =6,19,)),"000 0-00-00")7D(B2,7,6+(LEN (B2)=18)*2)," 0-00-00"),"emmdd")8=IF(LEN(B2)=1 5,"19"&MID(B2 ,7,6),MID(B2, 7,8))9=IF(LEN(B2)=1 5,19,"")&MID( B2,7,6+(LEN(B 2)=18)*2)提取性别女此为公式中第1种方法的结果公式1=IF(MOD(IF(LE N(B2)=15,MID( B2,15,1),MID( B2,17,1)),2)= 1,"男","女")2=IF(MOD(RIGHT (LEFT(B2,17)) ,2),"男","女")提取生肖蛇此实例引用的是出生日期公式=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(D2 )-4,12)+1,1)15位改18位110221196508152247注意此公式是数组。

从身份证号码中提取数据

从身份证号码中提取数据

从身份证号码中提取数据
利用函数,从身份证18号码中提取出生日期:
Mid函数是一个字符串函数,作用是从一个字符串中截取出指定数量的字符。

Mid函数的语法格式是:MID(文本,开始位置,字符数)。

我个人比较喜欢这个函数,因为在一串字符中,不管你需要这串字符哪个位置的字符用这个函数都可以截取出来。

图一:像下图中的表格,需要提取出他们的出生日期,10个人的身份证号码可以手动提取,如果是几百上千个可能就需要加班干了,今天教你们不用加班干的方法。

原图
在表格中输入公式=MID(E3,7,8),这个公式的意思是:“E3”单元格指的是需要截取的文本(即身份证号所在的列),7指的是截取的文本开始的位置(出生日期在号码中开始的位置),8指的是截取的文本的字符数(出生日期年月日共8位字符数),就是截取几个字符。

在这个表格里的意思就是:提取身份证号码里的出生日期,身份证号码有18位,出生日期是从身份证号码的第7位开始,包括年月日在一起,我需要截取8个字符数。

公式输入完成后,按Enter键回车,鼠标拖动下拉填充单元格,出生日期就全部提取出来了。

在excel中像这样的日期是无法进行运算的,必须要用“-”、“/”分隔出来的日期才是正规的日期格式。

在mid函数外面再嵌套一个text函数,=TEXT(MID(E3,7,8),'0000-00-00')所有的函数公式必须都在英文状态输入。

text函数的用法已经在之前的文章中介绍过了,有不明白的可以再去看一遍。

关于身份证号提取出生日期的例子到此就结束了,就不明白的朋友可以给我留言,谢谢认真学习的你,明天你会更好。

身份证提取出生年月日格式

身份证提取出生年月日格式

从身份证号码中提取出生年月,看似一个很简单的操作,却包含着很多技巧,如果数据量较大,采用一般的方法将很难完成,所以我们可以采用Excel分列法、Excel函数公式法、Excel智能填充法来完成:
一、Excel分列法。

1、复制身份证号码至“出生日期”列。

2、【数据】-【分列】-【固定宽度】-【下一步】。

3、在【数据预览】区单击鼠标左键,选择需要提取字符的开始位置和结束位置,并【下一步】。

4、选择【列数据格式】中的【不导入此列】;并选中需要删除的结尾部分,单击【不导入此列】;选中“出生年月”部分,单击【日期】并【完成】。

二、Excel函数公式法。

在目标单元格中输入公式:=TEXT(MID(C3,7,8),"00-00-00")。

解读:
1、Mid(字符串,起始位置,字符长度);因为身份证号码中的出生年月从第7位开始,长度为8。

2、Text函数的作用为:根据指定的数值格式将数值设置为文本;语法结构为:=Text(数值,格式代码),其中“00-00-00”为日期的格式代码。

三、Excel智能填充法。

1、在第一个目标单元格中输入第一个需要提取的出生年月,如“19970721”。

2、选定所有目标单元格,包括第一个已经输入内容的单元格。

3、快捷键Ctrl+E。

解读:Ctrl+E为“快速填充”的快捷键,其根据已经填充的单元格内容,结合数据源智能联想填充内容。

Excel表格身份证号码提取出生日期的公式

Excel表格身份证号码提取出生日期的公式

Excel表格身份证号码提取出生日期的公式
Excel表格身份证号码提取出生日期的公式
(A1表示身份证号码所在的列位置)
=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)回车→向下填充
1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);
2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。

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

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

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

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

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

从身份证号码提取出生日期

从身份证号码提取出生日期

从身份证号码提取出生日期、性别、年龄等信息早期的身份证号码为15位数字,现在使用的身份证号码为18位数字,它们的含义如下:(1)15位:1-6位为地区代码,7-8位为出生年份(2位),9-10位为出生月份,11-12位为出生日期,第13-15位为顺序号,这3位奇数为男,偶数为女。

(2)18位:1-6位为地区代码,7-10位为出生年份(4位),11-12位为出生月份,13-14位为出生日期,第15-17位为顺序号,同上,第18位为效验位。

在做员工档案管理的工作时,有时希望通过身份证号码,自动提取出各个员工的出生日期、性别、年龄等信息。

现在我们就通过几个公式,来得到这些信息。

1、出生日期=IF(LEN(A2)=15,CONCATENATE("19",MID(A2,7,2),"/",MID(A2,9,2),"/",MID(A2,11,2)),CO NCATENATE(MID(A2,7,4),"/",MID(A2,11,2),"/",MID(A2,13,2)))LEN函数是检测这个身份证号码的数字位数,然后通过IF判断函数对15位或18位的情况做出不同处理。

CONCATENATE函数将提取出的分别代表“年”、“月”、“日”的信息,以及分隔符“/”等内容,合并成一个文本字符串。

MID函数的做用是从身份证的某个位置开始,取出若干字符。

比如MID(A2,7,2),就是将身份证号码(放在A2单元格中),从左边数第七个字符开始,取出2个字符。

2、性别=IF(LEN(A2)=15,IF(VALUE(RIGHT(A2,3))/2=INT(VALUE(RIGHT(A2,3))/2),"女","男"),IF(VALUE(MID(A2,15,3))/2=INT(VALUE(MID(A2,15,3))/2),"女","男"))由于代表性别的数字中,偶数为“女”,奇数为“男”,所以在这个公式中用VALUE函数将取出的字符串变成数字,再除以2,看是否能整除,因为奇数是不能整除的。

身份证提取数据 的方法

身份证提取数据 的方法

EXCEL中如何从身份证号码提取出生年月日及性别一、分析身份证号码其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。

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

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

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

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

二、转换身份证号码格式我们先将学生的身份证号完整地输入到EXCEL表格中,这时默认为“数字”格式(单元格内显示的是科学记数法的格式),需要更改一下数字格式。

选中该列中的所有身份证号后,右击鼠标,选择“设置单元格格式”。

在弹出对话框中“数字”标签内的“分类”设为“文本”,然后点击确定。

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

假设学生的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B列。

1. 提取出生年月信息如果上交报表时需要填写出生年月日,我们需要关心身份证号码的相应部位即可,即显示为“19890807”这样的信息。

在D2单元格中输入公式=IF(LEN(C2)=15,"19"&MID(C2,7,6),MID(C2,7,8)),其中:文本函数MID返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。

语法:MID(text,start_num,num_chars)即MID(文本,开始字符,所取字符数),Text 是包含要提取字符的文本字符串。

EXCEL表中身份证号码年月日提取公式

EXCEL表中身份证号码年月日提取公式

编辑日期:2009年7月28日EXCEL表中身份证号码信息提取公式1、从身份证号码提取“年”份如:342622************提取1994,输入公式:=IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4)),回车确定。

2、从身份证号码提取“月”份如:342622************提取11,输入公式:=IF(LEN(H2)=18,IF(MID(H2,11,1)="0",MID(H2,12,1),MID(H2,11,2)),IF(MID( H2,9,1)="0",MID(H2,10,1),MID(H2,9,2))),回车确定。

3、从身份证号码提取“日”如:342622************提取8(不是08形式),输入公式:=IF(LEN(H2)=18,IF(MID(H2,13,1)="0",MID(H2,14,1),MID(H2,13,2)),IF(MID( H2,11,1)="0",MID(H2,12,1),MID(H2,11,2))),回车确定。

4、从身份证号码提取“年月日”如:342622************提取形如1994-11-08,输入公式:=IF(LEN(H2)=15,1900+MID(H2,7,2)&"-"&MID(H2,9,2)&"-"&MID(H2,11,2),MID( H2,7,4)&"-"&MID(H2,11,2)&"-"&MID(H2,13,2)),回车确定。

5、从身份证号码提取“年龄”如:342622************提取年龄为15岁(针对2009年),输入公式:=YEAR(NOW())-IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4)),回车确定。

Excel自动从身份证中提取信息方法

Excel自动从身份证中提取信息方法

Excel自动从身份证中提取生日、性别、年龄、生肖每年新入学的一年级学生,都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格,以备建立全省统一的电子学籍档案。

数百个新生,就得输入数百行相应数据,这可不是个轻松活儿。

有没有什么办法能减轻一下输入工作量、提高一下效率呢?其实,我们只需在Excel2003中将学生的身份证号完整地输入后,它就可以帮我们自动填好出生日期和性别。

现在学生的身份证号已经全部都是18位的新一代身份证了,里面的数字都是有规律的。

前6位数字是户籍所在地的代码,7-14位就是出生日期。

第17位“2”代表的是性别,偶数为女性,奇数为男性。

我们要做的就是把其中的部分数字想法“提取出来”。

STEP1,转换身份证号码格式我们先将学生的身份证号完整地输入到Excel2003表格中,这时默认为“数字”格式(单元格内显示的是科学记数法的格式),需要更改一下数字格式。

选中该列中的所有身份证号后,右击鼠标,选择“设置单元格格式”。

在弹出对话框中“数字”标签内的“分类”设为“文本”,然后点击确定。

STEP2,“提取出”出生日期将光标指针放到“出生日期”列的单元格内,这里以C2单元格为例。

然后输入“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”(注意:外侧的双引号不用输入,函数式中的引号和逗号等符号应在英文状态下输入)。

回车后,你会发现在C2单元格内已经出现了该学生的出生日期。

然后,选中该单元格后拖动填充柄,其它单元格内就会出现相应的出生日期。

如图1。

图1 通过上述方法,系统自动获取了出生年月日信息小提示:MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令,具体使用格式在EXCEL中输入MID后会出现提示。

STEP3,判断性别“男女”选中“性别”列的单元格,如D2。

从身份证号码提取归属地等相关信息,你还在纠结用什么公式?真的out了!

从身份证号码提取归属地等相关信息,你还在纠结用什么公式?真的out了!

从身份证号码提取归属地等相关信息,你还在纠结用什么公式?真的out了!关于Excel内将身份证号码提取归属地、生日、性别等,有以下两个方法:一、公式法获取全国身份证归属地信息表,然后用公式直接读取,方法比较简单,如下所示:1、身份证归属地获取可以在网上搜索,其实很多地方都能找到相关的信息,最好是直接的Excel表格式的,如果需要,也可以从文末分享的配套数据链接中下载。

如下图所示:2、公式读取归属地相关信息有了这个基础的数据表后,根据身份证号码提取户籍地就很简单了,公式如下(假设身份证号码在A2单元格):=VLOOKUP(LEFT(A2,6),'全国身份证号码属地(无港澳台)'!A:C,2,0)如下图所示:二、网络直查法上面的公式法虽然简单,但是有一个问题,就是获得的归属地基础信息表无法更新,而且大部分现在能找到的版本都不包含“撤县变市”的情况,所以,有些身份证号码无法识别。

因此,个人更加推荐使用Excel2016的新功能Power Query(Excel2010或Excel2013可到微软官方下相应的插件)进行网络直查,操作简单,而且信息全面。

具体方法如下:输入链接(红色部分为身份证号码,按实际需要修改),如下:/idsearch/index.asp?action=idcard&use rid=440924************确定后就得到相应的信息:通过简单的编辑,就可以将数据返回Excel里了,如下图所示:当然,如果希望能直接输入,然后刷新出结果,那么需要再学习一些Power Query功能的知识,入门系列请看《新整理Power Query 入门操作基础20篇【含配套材料下载】》。

以上是个人总结的提取身份证户籍地的两个方法,公式法比较简单,但可能会出现信息更新不及时的情况,使用Excel新功能Power Query进行网络直接查询的方法稍为复杂一点儿,但是也不难,而且信息更加完整,并能保持与网络最新信息的同步。

Excel表格身份证号码提取出生日期的公式

Excel表格身份证号码提取出生日期的公式

Excel表格身份证号码提取出生日期的公式(B2表示身份证号码所在的列位置)=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)回车→向下填充1. Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2. 15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。

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

一、提取出生年月:A、15位身份证号码:=MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2) 回车确认即可。

B、18位身份证号码:=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&”日”回车确认即可。

二、提取性别:18位身份证号码:=IF(MOD(MID(B2,17,1),2)=1,"男","女")回车确认即可。

*excel公式中=IF(MOD(MID(E4,17,1),2)=0,"女","男")是什么意思?IF是选择函数,当MOD(MID(E4,17,1),2)=0成立时,单元格显示“女”,否则显示“男”。

MOD是取模函数,即是一个求余函数,求MID(E4,17,1)除以2的余数。

实质是判断MID(E4,17,1)的奇偶性。

MID从一个文本字符串的指定位置开始,截取指定数目的字符。

MID(E4,17,1)是从E4单元格的文本中的第17个字符开始,取一个字符。

三、提取年龄:=year(today())-value(right(left(B2,10),4)) 回车确认即可。

身份证号码提取年龄、出生日期、性别等信息(出生日期分年月日、斜杠、短横三种格式,年龄三种格式)

身份证号码提取年龄、出生日期、性别等信息(出生日期分年月日、斜杠、短横三种格式,年龄三种格式)

年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日 年月日
// // // // // // // // // // // // // // // // // // // // // // // // // // // // // // // // // //
序号 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
姓名 张三 李四 王五
身份证验证 出生日期1 出生日期2 出生日期3 性别 年龄1 年龄2 年龄3 所属地区 513901200208207110 TRUE 2002年08月20日 2002/08/20 2002-08-20 男 16年2个月 16年 16 四川省眉山地区资阳市 512002200109157388 TRUE 2001年09月15日 2001/09/15 2001-09-15 女 17年1个月 17年 17 #N/A 512002200203187129 TRUE 2002年03月18日 2002/03/18 2002-03-18 女 16年7个月 16年 16 #N/A FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! FALSE 年月日 // -#VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

从身份证号中提取性别、出生年月和性别

从身份证号中提取性别、出生年月和性别

EXCEL中从身份证号中提取个人信息身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是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、DATE(year,month,day):返回在日期时间代码中代表日期的数字,year介于1904-9999之间2、IF(Logical,Value_if_true,Value_if_false)3、LEN(text):返回文本字符串中字条的个数4、MID(text,start_num,num_chars):从文本字符串中指定的起始位置返回指定长度的字符。

5、MOD(number,divisor):返回两数相除的余数。

6、RIGHT(text,num_chars):从一个文体字符串的最后一个字符开始返回指定个数的字符。

7、YEAR(serial_number):日期的年份值,一个1900-9999之间的数值.8、NOW():返回日期时间格式的当前日期和时间。

9、LEFT(text,num-chars):从一个文本字符串的第一个字符开始返回指定个数的字符。

在这里,我们假定身份证号码保存在C列(从C2单元格开始)中,性别和出生年、月、日分别保存在D列和E列中。

根据身份证号码提取出生日期,性别,年龄

根据身份证号码提取出生日期,性别,年龄

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

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

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

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

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

综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取:假如身份证号数据在C2单元格根据身份证号码求出生年月:=IF(LEN(C2)=15,“19"MID(C2,7,2)"-"MID(C2,9,2)"-"MID(C2,11,2),MID(C2,7,4)"-"MID(C2,11,2)"-"MID(C2,13,2))或者=TEXT(MID(J2,7,8),"0000-00-00")格式是1962-12-23=IF(LEN(C2)=15,"19"MID(C2,7,2)"年"MID(C2,9,2)"月"MID(C2,11,2) “日”,MID(C2,7,4)"年"MID(C2,11,2)"月"MID(C2,13,2)) “日”格式是1962年12月23日=IF(LEN(C2)=15,"19"MID(C2,7,2)MID(C2,11,2)MID(C2,13,2))格式是***-*****3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式:说明:公式中的C2是身份证号根据身份证号码求性别:=IF(LEN(C2)=15,IF(MOD(VALUE(RIGHT(C2,3)),2)=0,"女",""),IF(LEN(C2)=18,IF(MOD(VALUE(MID(C2,15,3)),2)=0,"女","男"),"身份证错"))男MID(C2,9,2) MID(C2,11,2),MID(C2,7,4)根据身份证号码提取出生日期,性别,年龄根据身份证号码求年龄=IF(LEN(C2)=15,109-VALUE(MID(C2,7,2)),if(LEN(C2)=18,2009-VALUE(MID(C2,7,4)),"身份证错"))。

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

身份证号里的信息提取
一、从身份证号中提取“出生年月日”
假定身份证号在单元格A1 ,是“xxxxxx20001212xxxx”。

先考虑身份证号是18位的,出生年月日位置固定,所以可以通过MID函数摘取。

=MID(A1,7,8)
意思是在A1 的文本里,从第7个字符开始,摘取8个字符。

这样会返回一个8位的文本(20001212)。

再通过TEXT函数改变字符格式:
=TEXT(MID(A1,7,8),"0000-00-00")
这样会返回2000-12-12。

不过这是一个文本格式,用这个格式是不能运算的。

用DATEVALUE函数就可以把文本改成可以运算的数值了。

=DATEVALUE(TEXT(MID(A1,7,8),"0000-00-00"))
记得要修改单元个格式来显示日期,不然就会看见数字36872,实际上就是2000年12月12日的数值。

如果你的数据里,全都是新的身份证号,直接用这个公式就可以了。

如果是15位的身份证,MID(A1,7,6)会返回一个6位的文本(YYMMDD)。

用TEXT函数改变字符格式的时候,记得年份只有两个位数,所以是:
=TEXT(MID(A1,7,6),"00-00-00")
为了确保DATEVALUE知道头四位是年份,所以我们要把头
两位加上。

如果年份是在00年到10年,就假定是20XX年,不然就是19XX年。

从A1 摘两位年份就是MID(A1,7,2),通过VALUE改成数字,再判断是否小于等于10:
=IF(VALUE(MID(A1,7,2))<=10,20,19)
用文本连接& 把上面六位的日期接上:
=IF(VALUE(MID(A1,7,2))<=10,20,19)&TEXT(MID(A1,7,6),"00 -00-00")
最后用DATEVALUE改成数值:
=DATEVALUE(IF(VALUE(MID(A1,7,2))<=10,20,19)&TEXT( MID(A1,7,6),"00-00-00"))
要把两个情况合并考虑,先用LEN函数决定A1 单元格的字符数,然后用IF函数,如果是18位,就用第一个公式;如果是15位,就用第二个公式;都不是就返回错误。

=IF(LEN(A1)=18,
DATEVALUE(TEXT(MID(A1,7,8),"0000-00-00")),
IF(LEN(A1)=15,
DATEVALUE(IF(VALUE(MID(A1,7,2))<=10,20,19)&TEXT(M ID(A1,7,6),"00-00-00")),
"身份证号码不正确"))
二、从身份证号上提取性别信息
选中C1单元格,输入公式:
=IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)=0,"女","男")
或=IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2) =1,"男","女"),
或=IF(MOD(IF(LEN(A1)=15,RIGHT(A1),MID(A1,17,1)),2) =1,"男","女")
输入完成后,按下“Enter”键进行确认,第1位持证人的性别则自动显示在C1单元格中。

上述第一个公式的含义分三层来理解:
①如果身份证号码是15位(LEN(A1)=15),则取其15位数字(MID(A1,15,1)的最后一位,否则取其17位数字(MID(A1,17,1)的最后一位。

②然后求取出来的数值除2后的余数
(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2))。

③如果(IF)余数为“0”
(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)=0),则显示“女”,否则显示“男”。

再次选中C1单元格,用“填充柄”将上述公式复制到C列下面的单元格中,即可显示出其他持证人的性别。

注意:如果将上述公式修改为:
=IF(IF(LEN(A1)=15,MID(A1,15,1),IF(LEN(A1)=18,
MID(A1,17,1),""))="","号码有错",
IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),IF(LEN(A1)=18,
MID(A1,17,1),"")),2)=1,"男","女")),
当输入的号码不是15位或18位,系统提示“号码有错”,则效果
更佳。

注意:修改后的公式与上面的公式含义相似。

三、年龄计算
方法一
=YEAR(NOW())-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4)) 15位的身份证号
=YEAR(NOW())-IF(LEN(A1)=18,MID(A1,7,4),MID(A1,7,6)) 18位的身份证号
方法二
要计算年龄,其实就是计算出生年月日和今天两个日期之间的
差。

首先,必须确保出生年月日是日期格式,如果不是,可以用DATEVALUE函数转换。

假定出生年月日在B1 单元格,根据要求不一样,计算年龄的
方法也不一样。

比如说,我们只需要计算多少岁,不足一岁的不算一岁,
直接用DATEDIF就可以了:
=DATEDIF(B1,TODAY(),"y")
DATEDIF(日期一,日期二,"y")可以直接计算日期一和日期二之间差了多少年。

由于函数要求日期一必须小于日期二,所以在计算年龄是,必须生日在前,当天日期在后。

TODAY( )直接返回系统日期,所以放在日期二的位置。

如果想要计算到某一特定日期的年龄,直接把日期放在日期二就可以了。

比如:
=DATEDIF(B1,DATEVALUE("2010-12-31"),"y")
如果计算的年龄需要包括年月日,就需要把几个数和在一起:计算年的差异:
=DATEDIF(B1,TODAY(),"y")
计算月的差异:
=DATEDIF(B1,TODAY(),"ym")
计算日的差异:
=TODAY()-DATE(YEAR(B1)+年的差异,MONTH(B1)+月的差异,DAY(B1))
由于DATEDIF函数在计算日的差异时有时候会出错(参考DATEDIF),所以我们直接把出生年月日变成同年同月,再计算差异。

公式YEAR(B1)+年的差异中,YEAR(B1)摘出出生年份,加上
年的差异就是还原年份。

同样,MONTH(B1)摘出出生月份,加上月的差异就是还原月份。

再用DATE(年,月,日)转成日期格式。

最后用TODAY()一减,就是日的差异了。

合并起来,计算日的差异,公式就是:
=TODAY()-DATE(YEAR(B1)+DATEDIF(B1,TODAY(),"y"), MONTH(B1)+DATEDIF(B1,TODAY(),"ym"),DAY(B1))
把上面的公式用文本连接符“&”(CONCATENATE)串联起来,就是:
=DATEDIF(B1,TODAY(),"y")&"岁"&DATEDIF(B1,TODAY(),"ym")&"月"&TODAY()-DATE(YEAR(B1)+DATEDIF(B1,TODAY(),"y"),
MONTH(B1)+DATEDIF(B1,TODAY(),"ym"),DAY(B1))&"日"
结果会显示为“X岁X月X日”
用上面的公式,把出生年月日改成入职时间,同样可以计算工龄。

相关文档
最新文档