房地产会计网

标题: excel中利用身份证号码（15或18位）提取出生日期和性别 [打印本页]

作者: 银河飞雪 时间: 2014-1-10 15:52
标题: excel中利用身份证号码（15或18位）提取出生日期和性别
excel中利用身份证号码（15或18位）提取出生日期和性别
需要的函数：
LEN(C6)=15：检查C6单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位；
INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数。
RIGHT:返回文本字符串最后一个字符开始指定个数的字符；
MID：返回文本字符串指定起始位置起指定长度的字符，MID(C6,7,2)表示：在C3中从左边第七位起提取2位数；
"19"&MID(C6,7,2)表示：在C3中从左边第七位起提取2位数的前面添加19；
……
&""&表示：其左右两边所提取出来的数字不用任何符号连接；
&"-"&表示：其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年mm月dd日，则可以把公式中的“-”分别用“年月日”进行替换就行了。
一、提取出生日期
如果我们要从一个人的身份证号码中批量提取其出生年月日，并表示成“yyyy-mm-dd”形式，可以这样做，假设身份证号码在C列，在D列中输入公式=IF(LEN(C6)=15,"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2),MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2))，这个公式的含义就是，当其检查到C6单元格中的数据是15位的时，就显示"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2)的计算结果，否则就显示MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2)的计算结果。如：若C6单元格中是441000500112418，在D6单元格中计算出的结果是“1950-01-12”；若C6单元格中是410005194511168794，在D6单元格中计算出的结果是“1945-11-16”。
二、提取性别
在E6单元格输入公式=IF(LEN(C6)=15,IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),"女","男"),IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),"女","男"))这个公式的含义是如果C6单元格是一个15位数，就显示IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),"女","男")的计算结果；否则，显示IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),"女","男"))的计算结果。
最后把D6和E6单元格的公式向下拉下来，在C6列输入身份证号码后，出生日期和性别可自动输入了，这样就减少了用户输入数据工作量，提高了办事效率！

作者: LJL51618 时间: 2014-1-11 11:21
先回复

作者: LJL51618 时间: 2014-1-11 11:21
后学习

作者: smile100 时间: 2014-1-13 08:16
学习了。。。。。

作者: 图兰朵 时间: 2014-1-13 11:20
楼主太强大了

作者: Water_apple 时间: 2014-1-13 15:07
先回复，后学习。

作者: 961608261 时间: 2014-11-26 15:56
学习一下，顶一下

作者: qys3982990 时间: 2014-12-25 09:15
谢谢分享！！！！！！！！！

欢迎光临房地产会计网 (https://www.fdckj.com/)