![](https://iknow-base.cdn.bcebos.com/lxb/notice.png)
根据身份证号码提取出生年月,在EXCEL中怎么设函数公式?
以2007版EXCEL为例,提取下图中身份证号码中的出生年月
用函数MID可以实现,MID函数共3个条件。步骤如下:
1、在D2单元格输入:=mid()
2、选中身份证号码所在单元格C2,选中后输入“,”。MID函数第1个条件完成
3、输入年月第1位数字在身份证号码中的位置,即第7位,然后输入“,”。MID函数第2个条件完成
4、输入需提取的年月的位数:年4位+月2位,即6位。MID函数第3个条件完成
5、按回车键完成,返回数值
6、鼠标放到D2单元格右下角,变成“十”字光标后下拉即可完成后排函数公式的复制
7、完成
要完成身份证号码中出生年月的提取首先应该知道身份证号码的组合特征
首先,根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定,公民身份号码是特征组合码,由十七位数字本体码和一位数字校验码组成。
排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。
所以,要提取出生年月就应该从身份证号码的第7位数开始提取6个字符(年4个字符+月2个字符)
MID函数的语法是:MID(text,start_num,num_chars)
其中参数text是包含要提取字符的文本串(即身份证号码所在单元格C2);
start_num是文本中要提取的第一个字符(即年月的第1位数字,就是身份证号码的第7位);
num_chars是指定希望MID从文本中返回字符的个数(年4个字符+月2个字符=6)。
C2单元格输入公式:=--TEXT(MID(B2,7,8),"0-00-00"),向下复制填充。一次性提取所有身份证号码对应的出生日期。
首先看MID函数:MID(B2,7,8)的意思就是从B2单元格的第7位开始截取,截取的字符长度是8位,得到的计算结果就是"19761204"。
经过text函数乔装打扮,文本字符串"19761204"就有了日期的样子了,但是这样的结果仅仅是掩人耳目,还不是真正的日期格式。Excel中凡是经过TEXT、MID、LEFT、RIGHT等函数计算后得出的数值是文本格式,一般情况下,不能参与后续的计算处理。
选择B列数据,单击【数据】【分列】,在弹出【文本分列向导 第1步,共3步】对话框中,选择“固定宽度”,单击下一步。
在【文本分列向导 第3步,共3步】对话框中,依次点击最左侧列,点选【不导入此列(跳过)】。点击最右侧列,点选【不导入此列(跳过)】。点击中间列,点选【日期】。目标区域选择C2,单击完成。
1.如果身份证号码是15位,则从第7位开始取两位数字,并在这两位数字前加“19”,后面加“-”,从第9位开始取两位数字,并在这两位数字后面加“-”,从第11位开始取两位数字。否则,从第7位开始取四位数字,并在这四位数字后面加“-”,从第11位开始取两位数字,并在这两位数字后面加“-”,从第13位开始取两位数字。
第一,身份证号码的组成。
当今的身份证号码由18位数字组成:前6位为地址码,第7至14位为出生日期码,第15至17位为顺序码,第18位为校验码。
15~17位为顺序号,其中第17位如果是单数为男性分配码,双数为女性分配码。即通过第17位数字,可以判断性别,奇数为男,偶数为女。
下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。首先,在excel中输入以下表格,实例如下面所示。
A B C D E
姓名 身份证号码 出生日期 性别 年龄
张三 510321197205030786 1972年05月03日 女 37
第二,根据身份证号码自动提取出生年月
在目标单元格输入公式:=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日",回车确定,单元格内会出现与身份证号码相关的出生日期。其它单元格的填充,不必一一输入公式,只需选中该单元格复制,在其它单元格内粘贴即可。
提示:MID函数是提供的一个“从字符串中提取部分字符”的函数命令。
第三,根据身份证号码自动提取性别的方法
在目标单元格输入公式:=IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女")
提示:IF函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。
RIGHT函数:根据所指定的字符数返回文本串中最后一个或多个字符。
第四,根据身份证号码自动提取年龄的方法
在目标单元格输入公式:=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),"#-00-00"),NOW(),"y"),回车确定。
如果有很多个多身证号码,其中有15位的还有18位的要怎么做呢?
15位和18位方法是一样的
=IF(LEN(B3)=15,MID(B3,7,2)&"年"&MID(B3,9,2)&"月"&MID(B3,11,2)&"日",MID(B3,7,4)&"年"&MID(B3,11,2)&"月"&MID(B3,13,2)&"日")