巧用公式提取身份证信息
EXCEL中如何提取身份证出生日期和性别信息以及检验身份证号码的正确性
1、检验身份证号码的正确性（1）录入前可以通过下面操作避免录入错误通用公式为：=IF(LEN(D6)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(D6,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT ("1:17")))),11)+1,1)=RIGHT(D6),IF(LEN(D6)=15,ISNUMBER(--TEXT(19&MID(D6,7,6),"#-00-00"))))你也可以将上述公式放在数据有效性中，防止录入错误的身份证号。
操作方法:选择需要输入身份证的全部单元格区域,比如D6:A10,点菜单"数据"-"有效性",在"允许"的下拉框中选择"自定义",在"公式"输入上面的15位和18位通用公式,确定以后即可。
注意:公式里的"D6"是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在C3:C20输入身份证号,则将公式里的"D6"改为"C3"。
另外,你也可以先设置好某单个单元格的数据有效性(这时公式的D6改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。
（2）录入后可以通过下面操作检测录入错误通用公式为：=IF(LEN(D6)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(D6,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(D6),IF(LEN(D6)=15,ISNUMBER(--TEXT(19&MID(D6,7,6),"#-00-00"))))将上面的公式放到B2单元格，如果结果为TRUE，则身份证号是正确的，结果为FALSE则是错误的。
身份证提取生日的公式 excel
身份证提取生日的公式 excel
一、
1、在目标（C2）单元格中输入公式=--TEXT(MID(B2,7,8),"0-00-00")，点击回车。
MID(B2,7,8)的意思就是从B2单元格的第7位开始截取，截取的字符
长度是8位。
2、点回车（Enter）,可以公式结果变成了33273
3、选中C2单元格，点击右键，选择数字格式---日期，就可以看到
日期就被提取显示出来了。
4、最后选中C2单元格，下拉后其他人员的出生日期也出来了。
二、
1、在目标单元格中输入公式：=TEXT(MID(C3,7,8),"00-00-00")。
2、在第一个目标单元格中输入第一个人出生年月的8位数字。
3、选取目标单元格，包括第一个输入内容的单元格，快捷键Ctrl+E。
4、【数据】-【分列】-【下一步】-【下一步】，选择【列数据格式】中的【日期(YMD)】并【完成】。
最新整理WPS表格提取身份证信息的方法详解
W P S表格提取身份证信息的方法详解身份证相关信息包括籍贯、性别、出生年月日、农历年、属相、生日、星座、出生至今年数、出生至今天数和判断证件有效性，那么怎样把这个信息都提取出来呢?想知道的朋友可以看看以下W P S操作技巧教程，或许对你有帮助！W P S表格提取身份证信息的方法一、原籍贯：(公式代码)=I F E R R O R(I F(L E N B(A2)=15,I F E R R O R(v l o o k u p(V A L U E(M I D(A2,1,6)),S h e e t4!$A:$B,2,0),V L O O K U P(V A L U E(M I D (A2,1,4)00),S h e e t4!$A:$B,2,0)),I F E R R O R(I F E R R O R( V L O O K U P(V A L U E(M I D(A2,1,6)),S h e e t3!$A:$E,5,0),V L O O K U P(V A L U E(M I D(A2,1,4)00),S h e e t3!$A:$E,5,0)),V L O O K U P(V A L U E(M I D(A2,1,2)0000),S h e e t3!$A:$E,5,0) )),证件无效)运算说明：此运算运用了i f e r r o r逻辑函数，主要判断后面的函数计算结果，如果经过后面所有函数的判断都无法得到结果的，就在此单元格显示证件无效。
(此函数在后面的单元格(除判断身份证有效单元格外)都用到此函数，同理，后面就不一一解释此函数的用法。
i f函数主要是判断a2单元格的字符数(l e n函数)是否满足15位，如果是15位的话，则从a2单元格里提取(m i d函数)前6位数字(从第一位提取，6个字符)在数据库中查找(v l o o k u p函数)满足此条件的原籍贯所在地，此单元中后面的v l o o k u p函数也是此用途。
电子表格中与身份证有关的公式计算与查询
说 明
很多朋友在日常的办公中，常用到要从身份证号码中提取一些相关信息，虽然以前网上也有一些相关的公式，但总觉得要么不够系统，要么不方便使用。
为此，笔者为了让广大朋友在使用中能迅速方便地得到所要的结果，归纳总结了这一系列的公式，当然这些公式也不一定是最简的公式，还望大家批评指正！
在使用中，你只需要做到在有原始身份证号码的单元的的右边插入一空白列，并从本文件中对应的表页上找到对应的需要结果，右击该单元格，并从弹出的快捷菜单中选择“复制”，再回到你的原来的文件中，在刚才插入的空白列中的某个单元格中右击，并在弹出的快捷菜单中选择“选择性粘贴”-“公式”即可，当然，某些时候你还得将该单元格的格式设置成需要的格式（如相应日期型等）
当你认为已得到所需要的结果后，最好将用刚才的公式得出的结果全部选中，然后右击，选择“复制”，再在原处再次右击，选择“选择性粘贴”-“数值”。
（这样做的目的是让那些单元格中的数值摆脱公式，不会再发生变化，而且可任意复制到另外的地方）
特别说明一：在输入原始身份证号码时请将该单元格的格式设置成文本格式。
特别说明二：最后一个功能表页“查所属行政区划”，其功能只能在本文件中相应表页中才能实现，因为它必须用到本文件中一个已经隐藏了的表页“行政区划表”。
特别说明三：某些表页中的公式，可能复制到你的表中会显示错误，那是因为其中的某些函数用到的“分析工具库”，而你的Excel还未开启“分析工具库”。
那么，请进行以下操作，单击菜单栏中的“工具”-“加载宏”-在“可用加载宏”列表中将“分析数据库”左边的方框中打上“√”-确定。
当你再次运用这些公式时就不会再出错了。
身份证号在Excel中的巧妙运用
可 能有 些 渎 者 会有 这 样 的 疑 问 ，身 份 证 第 １８位 校 验码 是 怎 么生 成 的呢？具 体来说 ，主要 分为 以下几 个 步骤 ：
１）将 身份证 号 的前 面 ｌ７位数 分 别乘 以不 同的系 数 从 第 １位 到第 ｌ７位 的系数 分 别为 ：７－９—１０—５—８—４—
３ ５１ １ ３０２１９８７０９Ｏ８Ｏ６２３ Ｉ女 １９８７年０９月０８日 ｌ ３Ｏ ｌ ２０４２／９／８ Ｉ免 ｌ处女座 Ｉ
＝ ＤＡＴＥＤＩＦ（一一ＴＥＸＴ（ＭＩＤ（Ａ２，７，８），”０—００— ００”），ＴＯＤＡＹ（），＂Ｙ”）或者
２、５１２９０ｌ １ ９８３１ ２０５３２７Ｘ Ｉ男 Ｉ １９８３￣－１ ２月０５Ｅｔ Ｉ ３４ ２０４３／１２／５＝＝＿。 Ｉ射至
。 ｉ ９至 ！璺 ！塑 垫 童 ’！璺！生 ！星旦璺璺ｌ Ｉ ！ Ｌ鱼 【 塞熙ｊ
＝ ＩＦ（Ｍ０Ｄ（ＭＩＤ（Ａ２，１７，１），２），”男”，”女 ”） 解 析：１）身份 证号的第 １７位是 性 别信息 ，奇 数 表 示男，偶 数表 示 女。 ２）ＭＩＤ（Ａ２，１７，ｌ１函数 用来 提取 第ｌ７位数 。 ３）ＭＯＤ函数计 算这个 字符与２相除 的余数 。 ４）用 ＩＦ函数 来判 断，如果 余 数 为１，为奇数 ，说 明 为男性 ；如 果余 数 为０，为偶 数 ，说 明为女性 ， ２．根据 身份 证号 计算 ｝Ｈ生 日期 （罔４）
目前广 泛使用 的二代 身份 证号共 有 ｌ８位 ，由１７位 数 字本 体码 加 ｌ二１位校 验码 组 成 （陶 １）。从 身份 证 号 的组 成来 蜕 ，主要 由四部 分 组 成 。一 是 地 址 码 （１—６ 他 ），包 含６位 数 字 。１—２位 是 省 份 代码 ．３—４位 是 城 市代 码 ，５—６位 是 区县 代 码 。二是 ｆＩ牛 Ｈ期 码 （７一ｌ４ 位 ），包含８位 数 字，表示 编码 对象 出生的年 、月 、日。 例如 ，１９９５年ｌ０月７号就 用 １９９５１００７表示 。三是 顺 序码 （１５—１７位 ），包 含３位 数 字 ，表 示在 同一地 址 码所 标 识 的区域 范 内 ，对 同年 、同月 、同 日ｆ“生 的人 编定 的顺 序号 ，其 巾第 １７位 的奇 数代 表 男性 ，偶 数 代 表女 性 、四是校验 码 （１８位 ），身份 证 的最 后一 位 ，根 据前 面１７位 的数 字码 ，按照特定 的算 法计‘算 ｆ｛Ｊ来 的。
Excel中常见与身份证有关的公式
Excel中常见与身份证有关的公式第一篇：Excel中常见与身份证有关的公式1.根据身份证号计算年龄=IF(D2<>“",DATEDIF(TEXT((LEN(D2)=15)*19&MID(D2,7,6+ (LEN(D2)=18)*2),”#-00-00“),TODAY(),”y“),)注意：D2单元格为身份证号，且为文本格式。
2.根据身份证号计算出生年月日第一种，计算出来的格式为××年××月××日=IF(LEN(D2)=15,”19“&MID(D2,7,2)&”年“&MID(D2,9,2)&”月“&MID(D2,11,2)&”日“,MID(D2,7,4)&”年“&MID(D2,11,2)&”月“&MID(D2,13,2)&”日“)或者=IF(LEN(D2)=15,19,”“)&TEXT(MID D2,7,8-(LEN(D2)=15)*2),”#年00月00日“)第二种，计算出来的格式为××年××月=IF(LEN(A2)=15,”19“&MID(A2,7,2)&”年“&MID(A2,9,2)&”月“,MID(A2,7,4)&”年“&MID(A2,11,2)&”月“)第三种计算出来的格式为2011/1/1 =MID(B11,7,4)&”/“&MID(B11,11,2)&”/“&MID(B11,13,2)3.根据身份证号计算性别=IF(MOD(IF(LEN(D2)=15,MID(D2,15,1),MID(D2,17,1)),2)=1,”男“,”女“)4.身份证号全部改为18位（输出正确18位），输入数组公式(ctrl+shift+enter结尾）：=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUM(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)5.以18位身份证为准输出15位身份证号：=IF(LEN(G2)=15,G2,LEFT(REPLACE(G2,7,2,),15))6.验证身份证号（G2为输入18位身份证号，H2为输出正确18位身份证号）：=IF(G2<>0,IF(LEN(G2)=15,”一代身份证号“,(IF(LEN(G2)=18,IF(G2=H2,”正确“,”未通过验证“),IF(LEN(G2)>18,”超过18位，请核查“,”身份证号不完整“)))),)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),”#-00-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)))第二篇：电子表格中根据身份证自动识别性别出生年月公式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中身份证信息提取的方法（五篇）
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中从身份证号码中提取出生年月、性别及年龄Excel中从身份证号码中提取出生年月、性别及年龄一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
1.根据身份证号自动出生年月日MID函数的作用是返回文本字符串中从指定位置开始的特定数目的字符（该数目由用户指定）。
语法：MID(text,start_num,num_chars)Text 是包含要提取字符的文本字符串。
Start_num 是文本中要提取的第一个字符的位置。
文本中第一个字符的start_num 为1，以此类推。
Num_chars 指定希望MID 从文本中返回字符的个数。
函数为：MID(A1,7,8)，显示的时候格式为YYYYMMDD。
如果想把出生年月日的格式写成YYYY-MM-DD的格式，那么函数可以写成如下格式：=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)2. 提取性别信息函数1：=IF((MOD(MID(A1,17,1),2)=0),"女","男")MID(A1,17,1)------从单元格A1中的第17位数字开始提取1位数字。
MOD(MID(A1,17,1),2)=0-----把提取的数字取余。
IF((MOD(MID(A1,17,1),2)=0),"女","男")-----若余数为0，则性别为女，否则为男。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
巧用公式提取身份证信息
在日常工作中，我们常常需要用到EXCEL表格。
在此分享几个提取身份证号码信息的公式，希望对大家有所帮助。
身份证号码中包涵了出生日期（7到14位），性别（17位）等信息，运用公式，我们可以方便的提取这些信息：
首先是计算性别公式：
=IF(MOD(LEFT(RIGHT(B5,2)),2),"男","女")
其中（LEFT(RIGHT(B5,2)),2）为提取右边第二位（即第十七位）数字，mod为除法公式，最后结算结果为单数则显示“男”，反之显示“女”。
接下来是出生日期提取公式：
=TEXT(MID(B5,7,8),"0年00月00日")
其中MID(B5,7,8)意为在B5栏第七个数字起提取8位数字，"0年00月00日"为格式命令。
年龄公式：
=DATEDIF(F5,TODAY(),"y")
这里利用了我们在F5单元格已经计算出来的出生日期，TODAY()是提取当前系统时间，整个公式含义为计算两者之间年份差。
若计算机系统时间有误，可将TODAY()改为预计贷款发放日期或借款人申请日期，例如申请日期输入在I19栏,则公式修改为：=DATEDIF(F5,I19,"y")。
此外身份证号码还可以提取原籍地等信息，鉴于日常使用较少，在此不做赘述。