身份证号里的信息提取身份证号里的信息提取.doc

身份证号里的信息提取 一、从身份证号中提取“出生年月日” 假定身份证号在单元格 A1 ，是 “xxxxxxxx”。 先考虑身份证号是18位的，出生年月日位置固定，所以可以通过MID函数摘取。 =MID(A1,7,8) 意思是在 A1 的文本里，从第7个字符开始，摘取8个字符。这样会返回一个8位的文本。 再通过TEXT函数改变字符格式： =TEXT(MID(A1,7,8),0000-00-00) 这样会返回 2000-12-12。不过这是一个文本格式，用这个格式是不能运算的。 用DATEVALUE函数就可以把文本改成可以运算的数值了。 =DATEVALUE(TEXT(MID(A1,7,8),0000-00-00)) 记得要修改单元个格式来显示日期，不然就会看见数字 36872，实际上就是2000年12月12日的数值。如果你的数据里，全都是新的身份证号，直接用这个公式就可以了。 如果是15位的身份证，MID(A1,7,6)会返回一个6位的文本（YYMMDD）。 用TEXT函数改变字符格式的时候，记得年份只有两个位数，所以是： =TEXT(MID(A1,7,6),00-00-00) 为了确保DATEVALUE知道头四位是年份，所以我们要把头两位加上。 如果年份是在00年到10年，就假定是20XX年，不然就是19XX年。 从 A1 摘两位年份就是MID(A1,7,2)，通过VALUE改成数字，再判断是否小于等于10： =IF(VALUE(MID(A1,7,2))=10,20,19) 用文本连接 把上面六位的日期接上： =IF(VALUE(MID(A1,7,2))=10,20,19)TEXT(MID(A1,7,6),00-00-00) 最后用DATEVALUE改成数值： =DATEVALUE(IF(VALUE(MID(A1,7,2))=10,20,19)TEXT(MID(A1,7,6),00-00-00)) 要把两个情况合并考虑，先用LEN函数决定 A1 单元格的字符数，然后用IF函数， 如果是18位，就用第一个公式；如果是15位，就用第二个公式；都不是就返回错误。 =IF(LEN(A1)=18, DATEVALUE(TEXT(MID(A1,7,8),0000-00-00)), IF(LEN(A1)=15, DATEVALUE(IF(VALUE(MID(A1,7,2))=10,20,19)TEXT(MID(A1,7,6),00-00-00)), 身份证号码不正确)) 二、从身份证号上提取性别信息 选中 C1单元格，输入公式： =IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)=0,女,男) 或 =IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)=1,男,女)， 或 =IF(MOD(IF(LEN(A1)=15,RIGHT(A1),MID(A1,17,1)),2)=1,男,女) 输入完成后，按下“Enter”键进行确认，第1位持证人的性别则自动显示在C1单元格中。 上述第一个公式的含义分三层来理解： ①如果身份证号码是15位（LEN(A1)=15），则取其15位数字（MID(A1,15,1)的最后一位，否则取其17位数字（MID(A1,17,1)的最后一位。 ②然后求取出来的数值除2后的余数 （MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)）。 ③如果（IF）余数为“0” （MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)=0）， 则显示“女”，否则显示“男”。 再次选中C1单元格，用“填充柄”将上述公式复制到C列下面的单元格中，即可显示出其他持证人的性别。 注意：如果将上述公式修改为： =IF(IF(LEN(A1)=15,MID(A1,15,1),IF(LEN(A1)=18, MID(A1,17,1),))=,号码有错, IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),IF(LEN(A1)=18, MID(A1,17,1),)),2)=1,男,女))， 当输入的号码不是15位或18位，系统提示“号码有错”，则效果更佳。 注意：修改后的公式与上面的公式含义相似。 三、年龄计算 方法一 =YEAR(NOW())-IF(LEN(A1)=15,19MID(A1,7,2),MID(A1,7,4)) 15位的身份证号 =YEAR(NOW())-IF(LEN(A1)=18,MID(A1,7,4),MID(A1,7,6)) 18