18位的身份证号码常用作人员资料中必备信息,在作为常用资料信息大量用于Excel中,按照我国身份证号码规范,18位身份证号隐藏着大量的信息。

excel身份证号提取大全,excel身份证号提取信息教程-趣帮office教程网

所以当我们获得了身份证号的时候,便可以从中提取人员的有关信息,包括籍贯、出生日期、性别等信息。在Excel中可以通过公式的运用快速提取出来相关信息。

一、提取出生日期

出生日期代码位于第7位至第14位,分别是前4位代表年份,其后2位代表月份,最后2位代表日期。

提取用到的公式:

TEXT(value, format_text):用特殊的格式转换文本。Value指的是目标数据,format_text指的是特殊转换格式,例如“m/d/yyyy”(年月日)、#.000(小数位保留3位)。

MID(text, start_num, num_chars):返回文本字符串中从指定位置开始的特定数目的字符。Text指的是目标文本,start_num指的是开始位置,num_chars指的是字符数。例如MID(”中华人民共和国”,3,2)=人民。

excel身份证号提取大全,excel身份证号提取信息教程-趣帮office教程网

提取出生日期公式:=TEXT(MID(C3,7,8),"0年00月00日")。

二、提取性别

性别代码位于身份证号码第17位,即倒数第2位。奇数代表男性,偶数代表女性。

提取用到的公式:

MID(text, start_num, num_chars):同上。

MOD(Number,Divisor ):返回两数相除的余数。其中Number指的被除数,Divisor指的是除数,例如MID(3,2)=3÷2=1(余数)。

IF(logical_test, [value_if_true],):指定条件返回指定结果。其中logical_test表达式,[value_if_true] 条件为真(true)时返回的结果,[value_if_false]条件为否(false)时返回的结果。例如if(A2>0,“大于”,”小于”)。

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

excel身份证号提取大全,excel身份证号提取信息教程-趣帮office教程网

三、提取籍贯

籍贯代码位于前6位,前2位代表省级代码,中2位代表市级代码,后2位代表县(区)级代码。相关的代码已成规则,比如11代表北京,12代表天津。

提取籍贯需要提前准备“全国省市区代码表”作为数据表源。

excel身份证号提取大全,excel身份证号提取信息教程-趣帮office教程网

提取用到的公式:

VLOOKUP(搜索值,搜索区域,搜索区域列数,逻辑值):依据给定的搜索值,在指定搜索区域内第1列相同的值返回对应列数的值。

excel身份证号提取大全,excel身份证号提取信息教程-趣帮office教程网

LEFT($D3,6)*1:指的是搜索值为身份证号D3中从左数前6位的字符,“*1”是因为身份证号在Excel中单元格格式为“文本”,通过与1相乘使其转换为数字,使其与搜索区域“全国省市区代码表”中第1列格式一致,同为数字。LEFT($D3,6)*1=110101。

从“全国省市区代码表”的A列至E列搜索区域的第1列与110101相同时,返回110101对应的第3列的数值“北京市”。逻辑值=0表现相似匹配即可,如果逻辑值=1表示精确匹配。

excel身份证号提取大全,excel身份证号提取信息教程-趣帮office教程网

其他市级、县(区)级代码同理。