Excel提取18位/15位身份证出生日期② |
您所在的位置:网站首页 › excel根据日期提取年月 › Excel提取18位/15位身份证出生日期② |
问题场景
从user表中的身份信息中提取用户的出生日期; 以下方法也可适用于提取其他数据,不仅是身份证信息; 以下图中数据都为测试数据,不具备真实性! 场景一user表中的18位身份证,提取出生日期。 不考虑15位身份证号,场景二会提及15位时的情况。用Ctrl+E提取填充。 第一步:将D2单元格【身份证信息列】的7-14位(出生日期)复制到F2单元格。 18位身份证信息的7-14位是出生日期。第二步:同时按Ctrl+E填充,最后结果。 注意:这种方式提取出的不是日期格式。用mid()函数和连接符号“&”提取 函数解释 =MID(text, start_num, num_chars) #从一个字符串中截取出指定数量的字符 #text被截取的字符,start_num从左起第几位开始,num_chars截取的长度是多少第一步:在F2单元格英文状态下输入:=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)。 公式解释:MID(D2,7,4)、MID(D2,11,2)、MID(D2,13,2)分别截取的出生年、出生月、出生日,再用&符号将年月日用“-”连接起来(用"/"连接年月日也可以)。用data()函数和mid()函数提取。 函数解释 =DATE(year,month,day) #将结果将设为日期格式,且year,month,day三个参数为年、月、日参数 #MID函数解释看上面方案二第一步:在F2单元格英文状态下输入:=DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2))。 公式解释:MID(D2,7,4)把D2单元格的数据从第7位开始,截取4位,是出生的年份,对应DATE()函数里的第一个参数:year,另外两个参数同理。用Text()函数和mid()函数提取。 函数解释 =TEXT(value,format_text) #通过格式代码向数字应用格式,进而更改数字的显示方式 #value为引用的单元格数值,format_text为单元格格式参数代码,这里只用一种"00-00-00",表示日期第一步:在F2单元格英文状态下输入:=TEXT(MID(D2,7,8),“00-00-00”)。 公式解释:MID(D2,7,4)把D2单元格的数据从第7位开始,截取8位,是出生的日期,再用text()函数用日期方式显示。user表中的同时存在18位或15位身份证,提取出生日期。 由于15位身份证号出生年只有2位,是7-8位,考虑到千年的因素(1900-1999,2000-2020(至今)),得先确定年份都是19开头还是20开头。假如19开头: 该情况不常见,下面不详细解释函数。用Text()、Len()和Mid()函数。 =TEXT((LEN(D2)=15)*19&MID(D2,7,6+(LEN(D2)=18)*2),"00-00-00") 第一步:在F2单元格中输入上述公式,鼠标移到F2单元格的右下角,出现实心的黑色【+】号,双击填充此公式。用IF()、Len()和Mid()函数。 =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)) 第一步:在F2单元格中输入上述公式,鼠标移到F2单元格的右下角,出现实心的黑色【+】号,双击填充此公式。len()函数筛选出身份证为15位和18位的,分别提取。 15位提取方法同18位提取方法原理一样。 总结实际业务场景复杂时,如果不能一次性解决问题,可择优使用、或可结合使用,多尝试,办法总比困难多! |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |