excel表中身份证号提取信息常用公式
如何从在excel中身份证号码里提取需要的信息
如何从在excel中身份证号码里提取需要的信息一、在excel中如何从身份证号码里提取出出生年月日及性别等假如,A1是姓名,B1是身份证号码,C1是出生年月可以用以下公式求出=IF(LEN(B2)=15,"19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日",MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日")在C2设置单元格格式为“日期”即可在C2中正确提取出其出生年月2EXCEL从身份证号码求出生年月日、性别及年龄公式2007-11-26 16:40EXCEL中用身份证号码求出生年月日及年龄公式2007年11月21日星期三 17:17一、分析身份证号码其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是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中身份证号码里提取个人信息这里,我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。
利用身份证号码提取个人信息
利用身份证号码提取个人信息利用身份证号码提取个人信息(如生日、性别、年龄、籍贯、是否成年)——excel超级应用,举一反三下面是我个人搜集整理的excel的应用方法,非常的有用,和大家一块分享。
利用身份证号码提取个人信息(身份证号码一定要正确,如果没有特别说明则身份证放在B列)1、利用身份证号码提取性别=IF(LEN(B2)=18,IF(MID(B2,17,1)/2=INT(MID(B2,17,1)/2),"F"," M"),IF(MID(B2,15,1)/2=INT(MID(B2,15,1)/2),"F","M")) 显示格式为:F、M(F为女、M为男)=IF(LEN(B2)=18,IF(MID(B2,17,1)/2=INT(MID(B2,17,1)/2),"女","男"),IF(MID(B2,15,1)/2=INT(MID(B2,15,1)/2),"女","男")) 显示格式为:男、女2、(推荐方法一:不用判断身份证位数)利用身份证号码提取出生年月(1)=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))显示格式为:1996-1-1、1996-12-1、1999-12-12、1997-2-18(2)=CONCATENATE(MID(B2,7,4),"-",MID(B2,11,2),"-",MID(B2,13,2))显示格式为:1996-01-01、1996-12-01、1999-12-12、1997-02-18(3)=IF(LEN(B2)=15,19&MID(B2,7,2)&"年",MID(B2,7,4)&"年")&IF(LEN(B2)=15,MID(B2,9,2)&"月",MID(B2,11,2)&"月")&IF(LEN(B2)=15,MID(B2,11,2)&"日",MID(B2,13,2)&"日") 显示格式为:1996年01月01日、1996年12月01日、1999年12月12日、1997年02月18日(方法二:需要判断身份证位数)提取出生年月信息身份证号写在A列。
和身份证有关的5个Excel函数公式
和身份证有关的5个Excel函数公式以下是一组和身份证有关的函数公式,如果你是财务人员或是一名HR,那就一起来看一下啦:1、查找重复身份证号码:=IF(COUNTIF(A:A,A2&"*")>1,"重复","")要点:在COUNTIF函数的第二参数后加上一个星号 &"*"。
COUNTIF函数在计算文本型数字时,会默认按数值型进行处理,但是Excel中的数字精度只有15位,并且身份证号码是18位,因此会把前15位相同的身份证全部识别为相同的内容。
在第二参数后加上一个星号 &"*",就是告诉Excel,要查找包含A2单元格内容的文本,通过这样变通的手段,COUTNIF函数就听话啦。
2、用出生年月来计算年龄:=DATEDIF(A4,TODAY(),"y")要点:DATEDIF函数用于计算两个日期之间的间隔。
用法是:=DATEDIF(开始日期,结束日期,指定要返回的类型)第三参数是Y,表示年,是M,就表示月。
TODAY函数返回系统当前的日期。
DATEDIF函数是隐藏函数,输入时没有屏幕提示,需要手工录入哦。
3、根据身份证号计算出生年月:=--TEXT(MID(A2,7,8),"0!/00!/00")要点:身份证号码中的第8位到15位是出生年月信息。
首先使用MID函数从A2单元格的第7位开始,提取出8个字符,得到类似19840727的结果。
再使用TEXT函数将这个字符串变成日期样式的文本,得到类似1984/07/27的结果。
由于TEXT函数的结果是文本型的内容,所以加上两个小减号 -- 就是计算负数的负数,通过四则运算,变成真正的日期序列。
公式也可以使用=--TEXT(MID(A2,7,8),"0-00-00"),原理是一样的。
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)) 回车确认即可。
excel身份证提取生日公式
excel身份证提取生日公式
在Excel 中可以使用以下公式来提取身份证号码列中的生日信息:=TEXT(MID(A1,7,8),"yyyy-mm-dd")
其中A1 为身份证号码所在的单元格,可以根据需要更改。
这个公式使用了Excel 函数MID() 和TEXT() 。
MID() 函数用于截取字符串中间的一部分,参数分别是:要截取的字符串、开始截取的位置和截取的长度。
这个公式中,MID(A1,7,8) 意思是从A1 中第
7 个字符开始,截取8 位字符。
TEXT()函数用来将数值转换成文本,并使用指定格式显示。
这里中,将截取的字符串转换成"yyyy-mm-dd"格式显示。
这样,就可以得到一个身份证号码列中生日信息的公式。
可以将其应用到整个列上,使用拖动或填充工具来快速提取其他单元格中的生日信息。
请注意,此公式假定身份证号码都是有效且符合标准格式,如果有部分身份证号码不符合格式或无效,公式的结果可能会有误差,建议预先处理这些数据。
电子表格中身份证号提取生日和年龄汇总
电子表格中身份证号提取生日和年龄汇总注:(以下公式假设身份证在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表中身份证号码年月日提取公式
编辑日期: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中身份证信息提取的方法【身份证信息提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)*1=IF(A2<>“",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)显示格式均为yyyy-m-d。
(最简单的公式,把单元格设置为日期格式)=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))显示格式为yyyy-mm-dd。
(如果要求为“1995/03/29”格式的话,将”-” 换成”/”即可)=IF(D4=”“,”“,IF(LEN(D4)=15,TEXT((”19“&MID(D4,7,6)),”0 000年00月00日“),IF(LEN(D4)=18,TEXT(MID(D4,7,8),”0000年00月00日“))))显示格式为yyyy年mm月dd日。
(如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd)=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),”19“&MID(A1:A2,7,6))显示格式为yyyymmdd。
=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)+0=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))=MID(A1,7,4)&”年“&MID(A1,11,2)&”月“&MID(A1,13,2)&”日“=IF(A1<>”“,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1) =18)*2),”#-00-00“))从身份证号码中提取出性别=IF(MOD(MID(A1,15,3),2),”男“,”女“)(最简单公式)=IF(MOD(RIGHT(LEFT(A1,17)),2),”男“,”女“)=IF(A2<>” ”,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,”男“,”女“)从身份证号码中进行年龄判断=IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MI D(A3,7,6+ (LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),)=DATEDIF(A1,TODAY(),“Y”)(以上公式会判断是否已过生日而自动增减一岁)=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY())-IF(LEN(A1)=15,”19“&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY() )-VALUE(MID(B1,7,4))&”岁“=YEAR(TODAY())-IF(MID(B1,18,1)=”“,CONCATENATE(”19“,MID(B1,7,2)),MID(B 1,7,4))按身份证号号码计算至今天年龄=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=1 8)*2),”#-00-00“),TODAY(),”y“)以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)*1,”2006-10-31“,”y“)按身份证号分男女年龄段按身份证号分男女年龄段,身份证号在K列,年龄段在J列(身份证号为18位)男性16周岁以下为男性16周岁(含16周岁)以上至50周岁为2 男性50周岁(含50周岁)以上至60周岁为3 男性60周岁(含60周岁)以上为女性16周岁以下为女性16周岁(含16周岁)以上至45周岁为2 女性45周岁(含45周岁)以上至55周岁为 3 女性55周岁(含55周岁)以上为=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,1 3,2)),TODAY(),”y“),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))=SUM(--(DATEDIF(MID(K1,7,4)&”/“&MID(K1,11,2)&”/“&MID(K1,13,2 ),TODAY(),”y“)>={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【年龄和工龄计算】根据出生年月计算年龄=DATEDIF(A1,TODAY(),”y“)=DATEDIF(A1,TODAY(),”y“)&”周岁“ =DATEDIF(A1,NOW(),”y“)根据出生年月推算生肖中国人有12生肖,属什么可以推算出来。
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)) 回车确认即可。
身份证号提取函数
我们假定用上述公式提取的信息在b2单元格中,则要在c2单元格中输入:
=IF(MOD(B2,2)=0,"女","男")
意思是:如果B2被2整除(mod为取模函数,模为0表示为偶数),则是女性,否则为男性。
到这里我们差不多了解了身份信息提取中的主要函数了。补充两个与身份证无关但很有用的函数:
如果手头有一个身份证信息表,通过excel的文本函数,可以快速提取出相关信息。
1.提取生日:(下面假定a2单元格中存放有身份证号码)
考虑到身份证仍然存在15位的,所以,得全盘考虑——使用条件判断。
使用公式“ =IF(LEN(A2)=18,MID(A2,7,8),"19"&MID(A2,7,6) ”来做即可。公式中if为条件判断,如果身份证号码长度是18位,则直接从第7位开始取,连续取8位,将得到形如19810523这样的生日信息;如果为15位,则需要在提取出的数字前加上“19”,&就是连接符号,而MID(A2,7,6)表示从第7位连续取6位。
2.取性别
15位号码的性别信息在倒数第的公式了:
=IF(LEN(A2)=18,MID(A2,17,1),MID(A2,15,1)
但以上仅仅取出的是性别代码数字,而并非性别文字本身,好在,身份证性别编码中,凡是奇数就表示为男性,为偶数则为女性,所以,只要再加上一个是否奇数的判断,则可在相应单元格中填写身份信息了。
②如果你需要将手机号码的后四位数显示为*号,这是就该考虑用replace()了。研究一下以下公式吧:
=REPLACE(A2,8,4,"****")
手机号码中从第8位开始的连续四位数将变更为*号。
身份证提取出生年月日通用公式
1.打开输入有身份证号码的excel表格,其实身份证号码中,从第7位开始之后的8位数字就是出生年月日,有规则所以可以利用公式提取。
2.在身份证数据列右侧列空白单元格中输入“=mid”,然后双击MID调起函数。
3.第一个参数选择需要提前出生年月日的身份证号所在单元格,第二个参数输入7,第三个参数输入8,参数之间以逗号隔开(公式的意思就是在单元格数据中,从第7位开始提前8位数字)
4.按回车键就可以提前出对应的出生年月日
5.通过鼠标下拉填充可以得出其他列身份证号对应的出生年月日
简言之就是:
1、打开输入有身份证号码的excel表格,其实身份证号码中,从第7位开始之后的8位数字就是出生年月日,有规则所以可以利用公式提取
2、在身份证数据列右侧列空白单元格中输入“=mid”,然后双击MID调起函数
3、第一个参数选择需要提前出生年月日的身份证号所在单元格,第二个参数输入7,第三个参数输入8,参数之间以逗号隔开(公式的意思就是在单元格数据中,从第7位开始提前8位数字)
4、按回车键就可以提前出对应的出生年月日,然后通过鼠标下拉填充可以得出其他列身份证号对应的出生年月日。
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)) 回车确认即可。