当前位置:文档之家› 如何提取身份证号里面的出生日期、验证身份证号是否正确

如何提取身份证号里面的出生日期、验证身份证号是否正确

如何提取身份证号里面的出生日期、验证身份证号是否正确
如何提取身份证号里面的出生日期、验证身份证号是否正确

中国居民身份证号码是一组特征组合码,原为15位,现升级为18位,其编码规则为:

15位:6位数字常住户口所在县市的行政区划代码,6位数字出生日期代码,3位数字顺序码。

18位:6位数字常住户口所在县市的行政区划代码,8位数字出生日期代码,3位数字顺序码和1位检验码。

其中3位数字顺序码,是为同一地址码的同年同月同日出生人员编制的顺序号,偶数的为女性,奇数的为男性。

1、提取籍贯地区的行政区划代码(A2为身份证号,下同)

15与18位通用:=LEFT(A2,6)

如果有一个编码和省份地区的对照表,可以用VLOOKUP函数来提取地区信息。

2、提取出生日期信息

15位:=--TEXT(19&MID(A2,7,6),"#-00-00")

18位:=--TEXT(MID(A2,7,8),"#-00-00")

15与18位通用:

=--TEXT(IF(LEN(A2)=15,19,"")&MID(A2,7,6+IF(LEN(A2)=18,2,0)),"#-00-00") 简化公式:=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")

(请将输入公式的单元格格式设置为日期格式)

3、提取性别信息

15位:=IF(MOD(RIGHT(A2),2)=1,"男","女")

18位:=IF(MOD(MID(A2),17,1)=1,"男","女")

=IF(MOD(MID(D2,17,1),2)=1,"男","女")

15与18位通用:=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,"男","女")

简化公式:=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")

4、检验身份证号码的正确性

18位身份证号码的最后一位是检验码,它是根据身份证前17位数字依照规则计算出

来的,其值0~9或X。一般情况只要有一位数字输入错误,依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况,但这种情况出现的可能性较低。因此,对18位号码的验证采用如下公式:

=MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(1 8-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2,1)

对于15位身份证,由于没有检验码,我们只能简单地去判断出生日期代码是否是一个有效的日期,避免输入一些像“731302”或“980230“等这样不存在的日期。

=ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))

综合15位和18位后的通用公式为:

=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:1 7")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))))

由于目前15位身份证号码已经很少了,如果对15位的号码不需要作进一步的判断,则公式可以简化成:

=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:1 7")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),LEN(A2)=15)

将上面的公式放到B2单元格,如果结果为TRUE,则身份证号是正确的,结果为FALSE 则是错误的。

你也可以将上述公式放在数据有效性中,防止录入错误的身份证号。操作方法:选择需要输入身份证的全部单元格区域,比如A2:A10,点菜单"数据"-"有效性",在"允许"的下拉框中选择"自定义",在"公式"输入上面的15位和18位通用公式,确定以后即可。注意:公式里的"A2"是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在C3:C20输入身份证号,则将公式里的"A2"改为"C3"。另外,你也可以先设置好某单个单元格的数据有效性(这时公式的A2改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。

5、15位升为18位

=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(RE PLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2) 6、18位转换为15位

=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)

excel 怎样从身份证号码提取年龄和性别

excel 怎样从身份证号码提取年龄和性别- [电脑应用技巧] 版权声明:转载时请以超链接形式标明文章原始出处和作者信息及本声明 https://www.doczj.com/doc/b210775174.html,/logs/50218662.html 因为自己需要,在网上找来了这个教程,函数真是好用的东西。这个教程很详细,不过我偷懒,因为自己觉得只需要看公式,所以用红字标记方便自己。。。。 在EXCEL中如何利用身份证号码计算出生年月、年龄及性别 在学校的人事管理中经常会遇到需要统计教职工的年龄的问题,但案头的原始资料只有身份证号码,其实这足够了。在EXCEL中,引用其内置函数利用身份证号码达到此目的比较简单。1、身份证号码简介(18位): 1~6位为地区代码;7~10位为出生年份;11~12位为出生月份;13~14位为出生日期;15~17位为顺序号,并能够判断性别,奇数为男,偶数为男;第18位为校验码。 2、确定“出生日期”: 18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄” 应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。 ①正确输入了身份证号码。(假设在D2单元格中) ②将光标定位在“出生日期”单元格(E2)中,然后在单元格中输入函数公式 “=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。 关于这个函数公式的具体说明:MID函数用于从数据中间提取字符,它的格式是:MID (text,starl_num,num_chars)。 Text是指要提取字符的文本或单元格地址(上列公式中的D2单元格)。 starl_num是指要提取的第一个字符的位置(上列公式中依次为7、11、13)。 num_chars指定要由MID所提取的字符个数(上述公式中,提取年份为4,月份和日期为2)。 多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起,“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记,这样的数据以后就可以作为日期类型进行年龄计算。确定“年龄”:

Excel身份证提取生日性别年龄

方法一: 1.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,2007-VALUE(MID(B 2,7,4)),"身份证错")) 4.Excel表中用Year\Month\Day函数取相应的年月日数据;

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

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条

excel 根据身份证号码自动提取出生年月

excel 根据身份证号码自动提取出生年月、性别、年龄的方法 在excel表格中,根据身份证号码就可以自动提取出生年月、性别、年龄。 第一,身份证号码的组成。 当今的身份证号码由18位数字组成:前6位为地址码,第7至14位为出生日期码,第15至17位为顺序码,第18位为校验码。 15~17位为顺序号,其中第17位如果是单数为男性分配码,双数为女性分配码。即通过第17位数字,可以判断性别,奇数为男,偶数为女。 下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。首先,在excel中输入以下表格,实例如下面所示。 (身份证号码的输入方法见 A B C D E 姓名身份证号码出生日期性别年龄 张三510321************ 1972年05月03日女37 第二,根据身份证号码自动提取出生年月 =MID(C2,7,4)&"."&MID(C2,11,2)&"."&MID(C2,13,2)&"" 在目标单元格输入公式:=MID(B2,7,4)&"年"&MID(B2,11,2)&"月 "&MID(B2,13,2)&"日",回车确定,单元格内会出现与身份证号码相关的出生日期。其它单元格的填充,不必一一输入公式,只需选中该单元格复制,在其它单元格内粘贴即可。 提示:MID函数是提供的一个“从字符串中提取部分字符”的函数命令。 第三,根据身份证号码自动提取性别的方法 在目标单元格输入公式:=IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女") 提示:IF函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。 RIGHT函数:根据所指定的字符数返回文本串中最后一个或多个字符。 第四,根据身份证号码自动提取年龄的方法 在目标单元格输入公式: =DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),"#-00-00"),NOW(),"y"),回车确定。 如何从身份证号码中提取出生年月 在处理人员信息表格中,又要录入出生年月,还要输入身份证号。我们可以只输身份证号,通过一点技巧就能提取出生年月。方法如下: 要在下表中的出生年月中录入,1、先在I3单元格输入公式“=MID(D3,7,8)其中,I3表示转变的年令是放在I列的第三行,D3表示你要截取的数值源(即身份证号那一列,而且是第三行),7表示你要从第几位始截取),8表示你要截取的长度),向下复制拉动即可。

Excel表中身份证号码提取出生年月、年龄、性别的使用技巧[1]

Excel表中身份证号码提取出生年月、性 别、年龄的使用技巧 excle中当一个序列号变更,下面序列号自动变更的方法。 浏览次数:298次悬赏分:0 |解决时间:2011-3-11 12:48 |提问者:kasure 问题补充: 比如我编制了序列号001,002,003。。。。,然后我要是中间插入一行,比如在002和003之间插入一行,我下面的编号都要变动,如何实现这样的功能? 最佳答案 那我想知道如果你需要删除一行的话,下面的编号是否需要变动?如果都需要变动的话,你可以试试这样: 1、把序号列的单元格格式改成"000"(在设置单元格格式--自定义--类型那里可以改) 2、把序列号的单元格填上公式=row() 。如果表格上面有表头的话,你数数表头有多少行,在公式后面减去行数,例如有5行表头,公式就是=row()-5 当你插入行的时候把公式填上就可以了 方法一: 1.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)&"-"&M ID(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(A 1,7,4)&"-"&MID(A1,11,2))

如何用EXCEL提取身份证号的出生日期并计算年龄

如何用EXCEL提取身份证号的出生日期并计算年龄 身份证号码的数字都有其具体的意义,从这些数字中可以获得该号码所对应人员的出生户籍地、出生日期、性别等基本信息。利用EXCEL函数,可以很方便的提取身份证号码中的出生日期信息,并计算出年龄。 工具/原料 ?电脑 ?OFFICE2003版或其他更高版本 方法/步骤 1.身份证号码的结构和意义:(1)前6位是地址码,一般为出生上户口的 时候户籍地的代码;(2)第7至第14位是出生日期码,是按年月日排列的; (3)第15至第17位是顺序码,是由公安机关编订的号码,用来区别同一户

籍地同年同月同日生的人,其中第17位是性别信息,奇数为男性,偶数是女性;(4)第18位即最后一位是校验码,由前面17位数字根据全国同一的算法进行计算得出。 2.打开示例EXCEL文件,该表有4个栏目:身份证号、姓名、出生日期、 年龄。出生日期和年龄需要通过EXCLE进行提取和计算,该2列的单元格格式为“常规”格式。

3.提取出生日期需要使用的函数有两个,一个是DATE函数,一个是MID 函数。DATE函数的结构为DATE(year,month,day),其作用是将提取的数字变为日期格式进行显示。MID函数的结构为MID(text, start_num, num_chars),其作用是从字符串中提取指定位数的字符,其中text是需要提取的字符所在的字符串,start_num是需要提取的字符在该字符串中的起始位置,num_chars是需要提取的字符的位数。

4.在C3单元格输入公式 “=DATE(MID(A3,7,4),MID(A3,11,2),MID(A3,13,2))”并下拉,其中MID(A3,7,4)用于提取年份,MID(A3,11,2)用于提取月份,MID(A3,13,2)用于提取日期。 5.不需要精确到日期的年龄计算可以直接用需计算的年份减身份证号的年 份来得出。如要算所有人在2016年时的年龄,在C3单元格输入公式“=2016- MID(A3,7,4)”即可。

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

用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文件,另存为××文件,即可使用。 谢谢你的使用。 水晶六彩

通过身份证号码提取年龄 性别 出生年月

通过身份证号码提取年龄性别出生年月 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)) ----------------------------------------------------------------------------------------- -------------- 这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供参考: 说明:公式中的B2是身份证号 1、根据身份证号码求性别: =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18份证错")) 2、根据身份证号码求出生年月: =IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B 份证错")) 3、根据身份证号码求年龄:

2种方法,在身份证号中提取出生年月日

2种方法,在身份证号中提取出生年月日 教程背景:公司最近新入职了20名员工,已知每个员工的身份证号,现需要将员工的出生年月日统计出来,纳入公司的福利数据库,以便在员工生日当天为员工发放生日福利。 员工身份证号▼ 方法一:函数法 MID函数与TEXT函数的配合使用) MID函数:用于从字符串的指定位置开始,提取特定数目的字符串。 Text函数:表示将数值转化为自己想要的文本格式。

身份证号码为18位,第7位到第14位这8个数字为出生年月日。 步骤一、使用公式“=MID(C2,7,8)”就是从C2单元格的第7位开始,提取8位数字,结果为'19870507'。 步骤一得到的结果▼ 步骤二、再使用公式“=TEXT(MID(C2,7,8),'0-00-00'),将这个字符串变成 '0-00-00'的样式,结果为'1987-05-07'。 步骤二得到的结果▼

步骤三、现在'1987-05-07'只是有了日期的外形,但是本身还是文本型的,变成日期型的还需要在最前面加两个负号“--”,通过两次“负”的运算,就成为了真正的日期格式了。最终公式为“=--TEXT(MID(C2,7,8),'0-00-00')”。 步骤三得到的结果▼

可能细心的读者发现了,步骤二和步骤三得到的结果看起来是一样的啊,为什么要多此一举呢?这个地方用“=”公式检验一下就可以知道是不是一样的呢。 公式验证▼

由此可见,步骤二的结果和步骤三的结果完全不一样,步骤二是文本格式,步骤三是日期格式,当我们把年月日变为日期格式后,在使用其他数据透视表或者其他工具时,日期格式的数据才能被识别进行相应的运算。 方法二:分列 直接使用工具栏数据-分列 步骤一、利用分列选择“固定宽度”。 固定宽度▼

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

假设数据在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位开始的

EXCEL根据身份证号码自动提取年龄

EXCEL根据身份证号码自动提取年龄、性别、出生年月的公式(15位、18位通用) EXCEL根据身份证号码自动提取年龄、性别、出生年月的公式(15位、18位通用) 说明:以身份证号输入到B2单元格为例,如果身份证号输入到F4单元格,请把公式中的B2替换为F4。 1、提取年龄公式(15位、18位通用) 公式1:返回虚岁,生日前后返回同样的虚岁年龄 =IF(LEN(B2)=0,"",IF(LEN(B2)=15,year(now())-1900-VALUE(MI D(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))) 公式2:返回周岁,生日前后返回不同的周岁年龄 =IF(LEN(B2)=0,"",IF(LEN(B2)=15,DATEDIF(DATE(CONCATE NATE("19",MID(B2,7,2)),MID(B2,9,2),MID(B2,11,2)),TODAY(),"Y") ,IF(LEN(B2)=18,DATEDIF(DATE(MID(B2,7,4),MID(B2,11,2),MID(B 2,13,2)),TODAY(),"Y"),"身份证错"))) 函数说明:LEN返回文本字符串中的字符个数。 DATEDIF()计算两个日期之间的天数、月数或年数。 DATE返回代表特定日期的序列号。

CONCATENATE将多个文本字符串合并成一个文本字符串。 MID从文本字符串中指定的位置开始,返回指定长度的字符串。 TODAY返回日期格式的当前日期。 2、提取性别的公式(15位、18位通用) =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,"女","男"),IF(LEN(B2)=0,"","身份证错"))) 函数说明:LEN返回文本字符串中的字符个数。 MOD返回两数相除的余数。结果的正负号与除数相同。 VALUE将代表数字的文本字符串转换成数字。 3、提取出生年月公式(15位、18位通用) =IF(LEN(B2)=0,"",IF(LEN(B2)=15,CONCATENATE("19",MID(B 2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7, 4),".",MID(B2,11,2)),"身份证错"))) 函数说明:LEN返回文本字符串中的字符个数。 CONCATENATE将多个文本字符串合并成一个文本字符串。 MID从文本字符串中指定的位置开始,返回指定长度的字符串。

excel2003从身份证号提取年龄的函

excel2003从身份证号提取年龄的函 excel2003从身份证号提取年龄的函数使用教程: 身份证提取年龄步骤1:制字一个带身份证号的excel,为了简单一些,我假设了一个身份证号:410106************; 身份证提取年龄步骤2:我们用的公式是:=datedif(text(mid(a1,7,8),"#-00-00"),today(),"y"),我们先解释最里面的mid(a1,7,8);mid是一个截取文本部分字符段的函数,使用格式:mid(text,start_num,num_chars);它一般有三个参数,第一个参数表示引用的文本或文本单元格,第二个表示从第几个文本字符开始截取,第三个表示截取几个,像公式中的mid(a1,7,8),表示将a1里的文本字符串从第7个字符开始截取,共取8个字符,而组成的文本字符串,例子中:mid(a1,7,8)就是19740703; 身份证提取年龄步骤3:text是一个返回文本类型的函数,使用格式:text(value,format_text);一般有两个参数组成,前一个引号文本或文本单元格,后一个表示文本类型,公式中mid(a1,7,8)就是第一个参数,#-00-00就是第二个参数。也就是说将mid(a1,7,8)得到的结果19740703,改变成“#-00-00”形式的文本,在这里就是1974-07-03。 身份证提取年龄步骤4:函数today(),指的是取当日日期,如:2014-03-29;

身份证提取年龄步骤5:datedif函数用于计算两个日期之间的年数、月数和天数。其语法:datedif(date1,date2,code);其中date1:表示开始日期,date2:表示结束日期;code:表示返回两个日期的参数代码,若为y,便是计算两个日期的年数,若为m 便是计算两个日期的月,若为d便是计算两个日期的天。 看了excel2003从身份证号提取年龄的函数使用教程

Excel中根据身份证号码自动提取信息

Excel中根据身份证号码自动提取信息 在excel表格中,根据身份证号码就可以自动提取出生年月、性别、年龄。 第一,身份证号码的组成 当今的身份证号码由18位数字组成:前6位为地址码,第7至14位为出生日期码,第15至17位为顺序码,第18位为校验码。 15~17位为顺序号,其中第17位如果是单数为男性分配码,双数为女性分配码。即通过第17位数字,可以判断性别,奇数为男,偶数为女。 下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。首先,在excel中输入以下表格,实例如下面所示。 A B C D E 姓名身份证号码出生日期性别年龄张三 510321************ 1972年05月03日女37 第二,根据身份证号码自动提取出生年月 在目标单元格输入公式:=MID(B2,7,4)&"年"&MID(B2,11,2)&"月 "&MID(B2,13,2)&"日",回车确定,单元格内会出现与身份证号码相关的出生日期。其它单元格的填充,不必一一输入公式,只需选中该单元格复制,在其它单元格内粘贴即可。 提示:MID函数是提供的一个“从字符串中提取部分字符”的函数命令。 第三,根据身份证号码自动提取性别的方法 在目标单元格输入公式:=IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女") 提示:IF函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。 RIGHT函数:根据所指定的字符数返回文本串中最后一个或多个字符。 第四,根据身份证号码自动提取年龄的方法 在目标单元格输入公式: =DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),"#-00-00"),NOW(),"y"),回车确定。

从身份证中提取年龄

从身份证中提取年龄 输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中: LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证 号码的长度是否是15位。 MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字, MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字, IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示 如果C2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取自第9 位开始的四位数字。 如果需要显示为“70年12月”这样的格式,请使用DATE格式,并在“单元格 格式→日期”中进行设置。 2. 提取性别信息 输入公式“=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单元格显示为“男”,否则显示为“女”。 这是根据身份证号码(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)),"身份证错") 身份证号在A1,你在B1输入 =YEAR(TODAY())-MID(A1,7,4)

从身份证号中提取年龄

从身份证中提取出生年月日、性别、年龄 1、年龄: =DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(), "y") 2、性别: =IF(MOD(VALUE(MID(A1,17,1)),2)=1,"男","女") 3、年月日: =MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2) 这三个公式是18位身份证的 年龄:就是从身份证号中提取出出生年份,用当前的年份去减 性别:判断倒数第2位是奇数还是偶数 年月日:从身份证中提取出相应的年月日 如果重写这些公式 1.年龄: =IF(LEN(A1)=18,DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1 ,13,2)),NOW(),"y"),DATEDIF(DATE("19"&MID(A1,7,2),MID(A1,9,2 ),MID(A1,11,2)),NOW(),"y")) 2.性别: =IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,17,1)),2)=1,"男","女"),IF(MOD(VALUE(MID(A1,15,1)),2)=1,"男","女")) 3.年月日: =IF(LEN(A1)=18,DATE(MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1 ,13,2)),DATE("19"&MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11 ,2))) ☆从出生年月日中提取年龄☆ ①、=INT((DATE(2010,9,30)-B1)/365) ②、=IF(AND(B1<>""),2010-YEAR(B1)) “B1”指出生年月日栏

EXCEL中如何提取身份证出生日期

EXCEL中如何提取身份证出生日期、性别、检验身份证号 码的正确性 中国居民身份证号码是一组特征组合码,原为15位,现升级为18位,其编码规则为:15位:6位数字常住户口所在县市的行政区划代码,6位数字出生日期代码,3位数字顺序码。 18位:6位数字常住户口所在县市的行政区划代码,8位数字出生日期代码,3位数字顺序码和1位检验码。其中3位数字顺序码,是为同一地址码的同年同月同日出生人员编制的顺序号,偶数的为女性,奇数的为男性。 1、提取籍贯地区的行政区划代码(A2为身份证号,下同)15与18位通用:=LEFT(A2,6) 如果有一个编码和省份地区的对照表,可以用VLOOKUP函数来提取地区信息。2、提取出生日期信息 15位:=--TEXT(19&MID(A2,7,6),"#-00-00") 18位:=--TEXT(MID(A2,7,8),"#-00-00") 15与18位通用:=--TEXT(IF(LEN(A2)=15,19,"")&MID(A2,7,6+IF(LEN(A2)=18,2,0)) ,"#-00-00") 简化公式:=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00 ") (请将输入公式的单元格格式设置为日期格式)3、提取性别信息 15位:=IF(MOD(RIGHT(A2),2)=1,"男","女") 18位:

=IF(MOD(MID(A2),17,1)=1,"男","女") 15与18位通用:=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,"男","女") 简化公式:=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女") 4、检验身份证号码的正确性 18位身份证号码的最后一位是检验码,它是根据身份证前17位数字依照规则计算出来的,其值0~9或X。一般情况只要有一位数字输入错误,依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况,但这种情况出现的可能性较低。因此,对18位号码的验证采用如下公式: =MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17 ")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2,1) 对于15位身份证,由于没有检验码,我们只能简单地去判断出生日期代码是否是一个有效的日期,避免输入一些像“731302”或“980230“等这样不存在的日期。=ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00")) 综合15位和18位后的通用公式为: =IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW( INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1) =RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00")))) 由于目前15位身份证号码已经很少了,如果对15位的号码不

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)) 回车确认即可。

身份证号码换算年龄、性别、出生日期的公式

15位和18位的身份证号都合适的了。设内容如下: ........A列......B列......C列......D列.... 1 身份证号性别出生年月年龄 2(输入身份证号) 算年龄的: “=IF(OR(LEN(A2)=15,LEN(A2)=18),RIGHT(IF(IF(LEN(A2)=15,MID(A2,9,2),MID(A2,11,2) )*100+IF(LEN(A2)=15,MID(A2,11,2),MID(A2,13,2))>MONTH(TODAY())*100+DAY(TODA Y()),YEAR(TODAY())-IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2))-1,YEAR(TODAY())-IF(L EN(A2)=15,MID(A2,7,2),MID(A2,9,2))),2),"证号错误")” 算性别的: “=IF(LEN(A2)=15,(IF(OR(ABS(RIGHT(A2))=1,ABS(RIGHT(A2))=3,ABS(RIGHT(A2))=5,A BS(RIGHT(A2))=7,ABS(RIGHT(A2))=9),"男","女 ")),(IF(OR(ABS(MID(A2,17,1))=1,ABS(MID(A2,17,1))=3,ABS(MID(A2,17,1))=5,ABS(MID( A2,17,1))=7,ABS(MID(A2,17,1))=9),"男","女")))” 算出生年月的: “=DATE(IF(LEN(E3)=15,MID(E3,7,2),MID(E3,9,2)),IF(LEN(E3)=15,MID(E3,9,2),MID(E3,1 1,2)),IF(LEN(E3)=15,MID(E3,11,2),MID(E3,13,2)))” 只要把输入身份证号的单元格格式设为“文本”就是最关键的,准确。 注:A2、E3是身份证号所在列和行,应相应改动 另一种方法 =DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"00-00-00"),TODAY() ,"y" ) A1是身份证号,在B1中输入上面的公式试试 在Excel中按身份证号码提出一定年龄段的人出来 在Excel中,有一个几千人的工作表,现在我想把工作表中满70岁的人员提出来,能依靠的条件就是身份证。而身份证号有十八位号和十五位号两种。也就是说,要依据身份证号找出在36年之前出生的人,该如何操作。 假设你的表格中从A3单元格开始填写身份证号码,那么可在该列后面插入一列,即B列,在B3输入公式:=IF(LEN(A3)=18,MID(A3,7,4),"19"&MID(A3,7,2)) 。将公式拖动复制到需要的行。这时选中该列——右键——复制——在原位(B3)单击右键——选择性粘贴——选数值——确定(该步骤目的是将公式列变为数字列,便于筛选)。现在,选中第二行——数据——筛选——自动筛选——在B列的下拉菜单中选“自定义”——在“显示行”的相应框中输入“小于或等于”-“1936”——确定。所有在1936年及其以后出生的就筛选出来了。注意:以上公式既适合18位,又适合15位身份证号码提取年份。

如何使用Excel从身份证号码中提取出生日期

如何使用Excel从身份证号码中提取出生日期2009-02-27 22:52例如:从身份证420821************中提取出生日期来,如何快速得出? 呵呵,只需使用语句:=DATE(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2)) 【A1是身份证号码所在单元格】 date()函数,地球人都知道,日期函数;如输入今天的日期=today() 那么,mid函数是什么东东呢? MID(text,start_num,num_chars) Text 为包含要提取字符的文本字符串;Start_num 为文本 中要提取的第一个字符的位置。文本中第一个字符的start_num 为1 ,以此类推;Num_chars指定希望MID 从文本中返回字符的个数。 对身份证号码分析下就知道:420821************,出生日期是1992年2月6日;也就是 从字符串(420821************)的第7位开始的4位数字表示年,从字符串的第11位开始的2位数字表示月,字符串的第13位开始的2位数字表示日。呵呵,强悍吧! Excel中利用身份证号码(15或18位)提取出生日期和性别 需要的函数: LEN(C6)=15:检查C6单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位;INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数。 RIGHT:返回文本字符串最后一个字符开始指定个数的字符; MID:返回文本字符串指定起始位置起指定长度的字符,MID(C6,7,2)表示:在C3中从左边第七位起提取2位数; "19"&MID(C6,7,2)表示:在C3中从左边第七位起提取2位数的前面添加19; …… &""&表示:其左右两边所提取出来的数字不用任何符号连接; &"-"&表示:其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年mm月dd日,则可以把公式中的“-”分别用“年月日”进行替换就行了。

相关主题
相关文档 最新文档