[excel] 让Excel中自动从身份证号码中提取出生日 [复制链接]

银河飞雪

管理员

TA的每日心情

	开心 2023-11-28 10:15

签到天数: 85 天

[LV.6]常住居民II

电梯直达

1^#

发表于 2008-9-5 12:02:13 |只看该作者 |倒序浏览

身份证中包含一段生日信息，现在我们要使用Excel的函数提取这段数字，使他变为可用的出生年月日的形式。
我们将面临3个问题：1）怎样判断15位身份证号与18位身份证号的区别；2）如何提取生日这段数字；3）如何区别开年、月、日。
Excel中，MID返回文本字符串中从指定位置开始的特定数目的字符；CONCATENATE可以将几个文本字符串合并为一个文本字符串；IF执行真假值判断，根据逻辑计算的真假值，返回不同结果；LEN返回文本字符串中的字符数。这4个函数分别解决以上3个问题。我们设身份证号在A列，字段属性为“文本”；LEN函数设计在B列；IF函数设计在C列；MID函数设计在D列；CONCATENATE函数设计在E列。

1）怎样判断15位身份证号与18位身份证号的区别
首先我们判断身份证号的长度，然后与数字“15”比较，相同则是15位的身份证号，不相同则是18位的身份证号。
a）函数LEN
LEN返回文本字符串中的字符数。
语法：LEN(text)
Text 是要查找其长度的文本。空格将作为字符进行计数。
以本例说明：要判断身份证号的长度，变量Text应为身份证号，函数值为身份证号长度：15或者18。
函数具体如下：=LEN(A1)
将此函数输入单元格B1中，向下拖出Text值按B2、B3顺序排列的一列函数。

b）函数IF
IF执行真假值判断，根据逻辑计算的真假值，返回不同结果。
语法：IF(logical_test,value_if_true,value_if_false)
Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。
Value_if_true logical_test 为 TRUE 时返回的值。Value_if_true 也可以是其他公式。
Value_if_false logical_test 为 FALSE 时返回的值。Value_if_false 也可以是其他公式。
以本例说明：Logical_test为判断身份证号长度为15位的（即身份证号长度=15）还是18位的。如果身份证号是15位的，Logical_test是真（即可理解为这段公式是正确的，身份证号长度等于15），返回Value_if_true中的值或公式。如果身份证号是18位的，Logical_test是假（即可理解为这段公式是错误的，身份证号长度不等于15），返回Value_if_false中的值或公式。
函数具体如下：
=IF(B1=15,"15位","18位")
单独引用的，非单元格内的包含的文字字符串需加""。
将此函数输入单元格C1中，向下拖出Logical_test值按C2、C3顺序排列的一列函数。

2）提取生日这段数字
身份证中只有6位或者8位是反应生日信息的，我们需要去掉非生日的数字，只提取生日数字。
函数MID
MID返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。
语法：MID(text,start_num,num_chars)
Text 是包含要提取字符的文本字符串。
Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。
Num_chars 指定希望 MID 从文本中返回字符的个数。
以本例说明：15位的身份证号，从第7位起至第12位，这6位数为生日信息，Start_num为7，Num_chars为6。18位的身份证号则是Start_num为7，Num_chars为8。
函数具体如下： 15位身份证号 =MID(A1,7,6)
18位身份证号 =MID(A1,7,8)

在D1列中输入 =IF(LEN(A1)=15,MID(A1,7,6),MID(A1,7,8))
为了同时适用于A列中15位和18位的身份证号，用IF函数首先判断，A1中文本长度是否等于15，等于15则真，返回值MID(A1,7,6)，也就是15位身份证号的7到12位YYMMDD；不等于15则假，返回值MID(A1,7,8))，也就是18位身份证号的7到14位YYYYMMDD。向下拖出一列次函数。

3）区别开年、月、日
提取出来的身份证号是一连串的数字，年月日不够直观。而且长度参差不齐，15位的身份证号提取出来的数字只含年份的后2位。
函数CONCATENATE
语法：CONCATENATE (text1,text2,...)
Text1, text2, ... 为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。
以本例说明：
除了在年月日间添加符号"."以外，15位身份证号提出的6位数字开头需加19。单独引用的，非单元格内的包含的文字字符串需加""。
函数具体如下：
15位身份证号 =CONCATENATE ("19",MID(A1,7,2),".",MID(A1,9,2),".",MID(A1,11,2))
18位身份证号 =CONCATENATE (MID(A1,7,4),".",MID(A1,11,2),".",MID(A1,13,2))

同样，为了使此函数同时适用于15位于18位的身份证号提取中，加入判断函数。
在E1列中输入 =IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,2),".",MID(A1,9,2),".",MID(A1,11,2)),CONCATENATE(MID(A1,7,4),".",MID(A1,11,2),".",MID(A1,13,2)))。拖出一列来。出生日期形式都统一了。