|
我们的身份证号码中隐藏着很多信息，有时候我们为工作需要要从身份证号码中提取信息，如出生日期、年龄、性别、归属地和属相等，今天我们就讲一下提取这几种信息的方法； 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)即可； |
|
|
