提取身份证信息函数
看完动画之后,我没有吹牛吧!其实这个是vb封装vba自定义函数实现的,如果大家没有装'Excel完美工具箱就不能这么用,如果你装了9.70版,这个是最新版,前面的版本没有这个功能。你百度excel完美工具箱,就可以找到。
有的同学说,我没有装你的工具箱有方法吗?有,请看下面
一、提取籍贯
先申明,你要有一张号码对应的地址表,你也可以百度到,如果你没有,可以私聊我
公式=VLOOKUP(LEFT(A2,6),省市代码!A:B,2,0)
公式解释
vlookup有4个参数
第1参数:查找值
第2参数:数据源
第3参数:返回数据中的第几列
第4参数用0精确查找,用1模糊查查找
第4参数不用,也是模糊查找
第4参数前面的逗号留下是精确查找
LEFT(A2,6),从身份证左边提取6位作vlookup函数第1参数
二、提取出生日期
公式=TEXT(RIGHT(19&MID(A2,7,(LEN(A2)=18)*2 6),8),'0-00-00')
公式解释
(LEN(A2)=18)*2 6)如果身份证的长度等于18返回true,相当于1,1*2=2然后加上6等于8,也 就是提取8位
由于15身份证从7位开始没有19,所以只能提取6位
(LEN(A2)=18)*2 6)如果身份证的长度不等于18返回False相当于0,0*2=0然后加上6等于6,也 就是提取6位
RIGHT(19&MID(A2,7,(LEN(A2)=18)*2 6),8)为什么前面要&19呢?因为15位的没有?有的同学就会问,那么18位不是又多了19,是的,所以我在外面又嵌套了一个Right函数,你不多,我刚好提取8位,你多了的那个19我又取不到
最后我们在后面嵌套一个text函数,有的同学又会问,老师的你的text函数第2参数为什么不用“yyyy-mm-dd“,大哥你刚才right得到一串是文本型,不是日期型,当然不可以,所以你只能用text(第1参数,'0-00-00')
三、提取出生日期
公式=DATEDIF(C2,TODAY(),'Y')
公式解释
Datedif函数是一个隐藏函数
它有3个参数
第1参数起始日期
第2参数结束日期
第3参数两日期计算方式
'Y'计算年
'M'计算月
'D'计算日
'YM',忽略年,不足要借位
'MD'忽略月,不足要借位
'YD'忽略年月,不足要借位
另大家要记得第1参数是起始日期,第2参数是结束日期
第2参数一定要大于第1参数,否则会报错,也就是第2参数的日期一定要在第1参数的日期后面
四、提取性别
15的身份证决定男女的位是在第15位,奇数男,偶数女
18位的身份证决定男女的位是在第17位,奇数男,偶数女
公式=TEXT(-1^MID(A2,15,3),'女;男')
公式解释
MID(A2,15,3)从15开始取,取3位,15位后面没有了,当然只取到15位,而18位的从15位开始取,提取3位,取到17位上,我们现在关注是它们最后一位是奇数还是偶数,如果
-1^MID(A2,15,3),这里有一个知识点,-1的奇次方等于-1,-1的偶次方等于1,这样我们就把男女分开了
-1^MID(A2,15,3)如果等于1就是女
-1^MID(A2,15,3)如果等于-1就 是男
Text函数第2参数'女;男',记得中间是用分号分开,这里有一个知识点,自定义数字的格式分为4节,正数;负数;零;文本,如果只有两节,第1节是正数和零,第2节是负数
联系客服