身份证中包含一段生日信息,现在我们要使用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)))。拖出一列来。出生日期形式都统一了。 |
|