LEN函数
函数功能
LEN函数用于返回文本字符串中的字符数。
函数语法
LEN(text)
参数解释
text:必需。表示要查找其长度的文本。空格将作为字符进行计数。
实例1 判断输入的身份证号码位数是否正确
身份证号码一般为15位或者18位,利用LEN函数可以检验表格中的身份证号码位数是否符合要求,如果满足位数则返回空格,否则返回“错误”显示。
➊ 选中C2单元格,在公式编辑栏中输入公式:
=IF(OR(LEN(B2)=15,LEN(B2)=18),””,”错误”)
按“Enter”键即可检验出第一位人员的身份证号码位数是否正确。
➋ 将光标移到C2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可检验出其他人员的身份证号码的位数是否正确,如图1所示。
图1
公式解析
①LEN函数判断B2单元格中的字符串长度是否为15位和18位。
②OR函数判断是否满足步骤①的任何一个条件。
③ 当满足条件时返回空,否则返回“错误”显示。
实例2 提取公司部门人员的姓名
A列为公司部门人员名单,每个单元格中同时包含了部门和员工姓名,并且之间以冒号分割。使用MID函数配合FIND函数可以提取出员工姓名并显示在B列中。
➊ 选中B2单元格,在公式编辑栏中输入公式:
=MID(A2,FIND(“:”,A2)+1,LEN(A2))
按“Enter”键即可提取出第一位员工的姓名。
➋ 将光标移到B2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可快速提取出其他员工的姓名,如图2所示。
图2
公式解析
①使用FIND函数返回“:”号在A2单元格中的位置,加1表示提取的起始位置。
②LEN函数返回A2单元格字符串的长度。
③ MID函数从步骤①返回结果值开始提取,提取到结束位置处的字符串。
实例2 从身份证号码中提取完整的出生日期
从身份证号码中可以提取出完整的出生日期,但需要配合多个函数来实现,分别是IF函数、LEN函数、CONCATENATE函数和MID函数。
➊ 选中C2单元格,在公式编辑栏中输入公式:
=IF(LEN(B2)=15,CONCATENATE(“19″,MID(B2,7,2),”年”,MID(B2, 9,2),”月”,MID(B2,11,2),”按“Enter”键即可从身份证号码中提取员工“邹凯”的出生日期。
➋ 将光标移到C2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可从员工身份证号码中提取出所有员工的完整出生日期,如图3所示。
图3
公式解析
①统计B2单元格中字符串是否是15位。
②如果步骤①条件满足,分3次从B2单元格字符串中提取字符,分别是:第7、8位(年份);第9、10位(月份);第11、12位(日)。再使用CONCATENATE函数将提取的字符
与“19”、“年”、“月”、“日”相连接。
③ 如果步骤①条件不满足,分3次从B2单元格字符串中提取字符,分别是:第7、8、9、10位(年份);第11、12位(月份);第13、14位(日)。再使用CONCATENATE函数将提取的字符与“年”、“月”、“日”相连接。
实例4 从身份证号码中提取出生年份
由于身份证号码有15位和18位之分,因此要使用MID函数可以从身份证号码中提取出生年份,需要配合IF函数与LEN函数来实现。
➊ 选中C2单元格,在公式编辑栏中输入公式:
=IF(LEN(B2)=15,”19″&MID(B2,7,2),MID(B2,7,4))
按“Enter”键即可从身份证号码中提取员工“邹凯”的出生年份。
➋ 将光标移到C2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可从员工身份证号码中提取所有员工的出生年份,如图4所示。
图4
公式解析
①统计B2单元格中字符串是否是15位。
②从B2单元格字符串中提取(从第7位开始提取,提取两位),并将提取的字符与19相连接。
③ 如果B2单元格中字符串不是15位,则从B2单元格字符串的第7位开始提取,提取四位。
实例5 从身份证号码中判断出性别
身份证号码中包含有持证人的性别信息,如果需要从身份证号码中返回性别,需要配合多个函数来实现,分别是IF函数、LEN函数、MOD函数和MID函数。具体操作如下:
➊ 选中C2单元格,在公式编辑栏中输入公式:
=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,”男”,”女”),IF (MOD(MID(B2,17,1),按“Enter”键即可从身份证号码中获取第一位员工的性别信息。
➋ 将光标移到C2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可从员工身份证号码中获取所有员工的性别信息,如图5所示。
图5
公式解析
①统计B2单元格中字符串是否是15位。
②提取B2单元格字符串的第15位。
③ 判断步骤②中提取的值是否能被2整除,如果能整除返回性别“女”,不能整除返回性别“男”。
④ 如果B2单元格中字符串不是15位,则提取B2单元格字符串的第17位。
⑤ 判断步骤④中提取的值是否能被2整除,如果能整除返回性别“女”,不能整除返回性别“男”。
LENB函数
函数功能
LENB函数用于返回文本字符串中用于代表字符的字节数。
函数语法
LENB(text)
参数解释
text:要查找其长度的文本。空格将作为字符进行计数。
实例1 返回文本字符串的字节数
➊ 选中B2单元格,在公式编辑栏中输入公式:
=LENB(A2)
按“Enter”键即可返回A2单元格中文本字符串的字节数。
➋ 将光标移到B2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可返回其他单元格文本所对应的字节数,如图6所示。
图6