快好知 kuaihz

Excel:提取身份证号里的生日、性别、生肖、...

S姐有几个压箱底儿的技能,就是能用 Excel,从成千上万的身份证号码里,瞬间提取出生年月、年龄、性别甚至生肖和星座!今天,S姐就将毕生所学传授给各位,正所谓:Excel 至尊,批量提取,号令表界,莫敢不从!!(强烈建议收藏!)

温馨提示:本文操作以 Office 2016 为例进行操作,其它版本操作或有略微差异。

一、3 招批量提取出生年月

1、最低门槛提取方式:快速填充

因为身份证号的7-14位为具体出生年月,手动输入至少两个人的出生年月信息后即可操作。

具体操作:

在C2、C3中【手动输入出生年月】→选中【C2到C12】→按下【CTRL+E】,快速填充。

2、最艺术提取方式:分列提取

说出来你可能不信,这种提取方式需要的是艺术细胞。只要你会画画,就会做分列提取

具体操作:

第一步:选中要提取的数据→【数据】→【分列】→选择【固定宽度】→下一步;

第二步:在刻度上截出出生日期的宽度,即【左边在6、7位之间】,【右边在14、15位之间】→下一步;

第三步:依此单击上一步中分割的三部分数据,并设置【列数据格式】,选中时该部分数据预览变为黑色。左侧及右侧数据设置为【不导入此列(跳过)】,中间出生日期数据设置为【日期】,同时标明目标区域,确定即可。

3、最理工男提取方式:函数

具体操作:

第一步:确保目标区域(即 C2:C12)格式为【日期】;

第二步:在 C2 输入公式

=--TEXT(MID(B2,7,8),"0-00-00")

向下拖拽填充,即可瞬间填充所有生日数据!

S姐来分解下这个公式,便于大家理解:

① MID(B2,7,8),说的是截取字段,即:从B2单元格的第7位开始截取,共截取8位字符,也就是 19370918;

② 然后加入 TEXT 函数,TEXT(MID(B2,7,8),"0-00-00"),在刚才截取的字符串 19370918 从右边数,后两位和后四位前面各加上短横线,变为 1937-09-18;

③ 但到了这一步,这个字符串还只是看起来像日期,实际只是文本。TEXT 函数前加入【--】,就是进行减负运算,将文本格式数值变成真正的日期格式,方才大功告成!

二、1 个函数轻松算年龄

借助 Datedif 函数,在我们刚才提取的出生年月基础上,就可以轻松计算出年龄啦。

年龄计算公式

=DATEDIF(C2,NOW(),"y")

解析:C2 为出生日期所在单元格。

三、Mod 函数秒辨男女

性别提取公式:

=IF(MOD(MID(B2,17,1),2),"男","女")

解析:身份证号的第17位代表的是性别,奇数为“男”,偶数为“女”。我们先用 Mid 函数提取第17位数字,再使用 Mod 函数判断奇偶,最后使用 IF 函数判断男女。

四、“大牛”函数秒知生肖

如果说上面的公式你都觉得毛毛雨,那么,下面S姐要介绍的生肖提取公式,可以堪称大牛!

生肖提取公式:

=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(C2),12)+1,1)

五、“大神”函数搞定星座

通过出生日期结果,还可以直接生成星座。公式有点长,其中的C2为出生日期所在单元格,小可爱们在使用的时候,可以直接复制公式,修改对应单元格即可。

星座提取公式:

=LOOKUP(--TEXT(C2,"mdd"),{101,"摩羯";120,"水瓶";219,"双鱼";321,"白羊";420,"金牛";521,"双子";621,"巨蟹";723,"狮子";823,"处女";923,"天秤";1023,"天蝎";1122,"射手";1222,"摩羯"})&"座"

好啦,女侠S姐这次给大家传授的技能就是这些,各位表哥表姐的速速收藏学习,告别加班指日可待!加油ヾ(◍°∇°◍)ノ゙

S姐的压箱底儿技能好看吗?

本站资源来自互联网,仅供学习,如有侵权,请通知删除,敬请谅解!
搜索建议:提取  提取词条  生肖  生肖词条  证号  证号词条  性别  性别词条  身份  身份词条  
办公

 Word表格技巧分享,你知道几个

Word表格使用频繁,有了这些小技巧,让办公更加轻松。分享职场干货,让你远离加班,记得关注哦。1、表格拆分合并表格拆分成两个部分,可以将光标置于要拆分的位置上,...(展开)