LEN函数

函数功能

LEN函数用于返回文本字符串中的字符数。

函数语法

LEN(text)

参数解释

text:必需。表示要查找其长度的文本。空格将作为字符进行计数。

实例1 判断输入的身份证号码位数是否正确

身份证号码一般为15位或者18位,利用LEN函数可以检验表格中的身份证号码位数是否符合要求,如果满足位数则返回空格,否则返回“错误”显示。

➊ 选中C2单元格,在公式编辑栏中输入公式:

=IF(OR(LEN(B2)=15,LEN(B2)=18),””,”错误”)

按“Enter”键即可检验出第一位人员的身份证号码位数是否正确。

➋ 将光标移到C2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可检验出其他人员的身份证号码的位数是否正确,如图1所示。

Excel文本函数LEN/LENB用法和实例教程-天天办公网

图1

公式解析

Excel文本函数LEN/LENB用法和实例教程-天天办公网

①LEN函数判断B2单元格中的字符串长度是否为15位和18位。

②OR函数判断是否满足步骤①的任何一个条件。

③ 当满足条件时返回空,否则返回“错误”显示。

实例2 提取公司部门人员的姓名

A列为公司部门人员名单,每个单元格中同时包含了部门和员工姓名,并且之间以冒号分割。使用MID函数配合FIND函数可以提取出员工姓名并显示在B列中。

➊ 选中B2单元格,在公式编辑栏中输入公式:

=MID(A2,FIND(“:”,A2)+1,LEN(A2))

按“Enter”键即可提取出第一位员工的姓名。

➋ 将光标移到B2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可快速提取出其他员工的姓名,如图2所示。

Excel文本函数LEN/LENB用法和实例教程-天天办公网

图2

公式解析

Excel文本函数LEN/LENB用法和实例教程-天天办公网

①使用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所示。

Excel文本函数LEN/LENB用法和实例教程-天天办公网

图3

公式解析

Excel文本函数LEN/LENB用法和实例教程-天天办公网

①统计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所示。

Excel文本函数LEN/LENB用法和实例教程-天天办公网

图4

公式解析

Excel文本函数LEN/LENB用法和实例教程-天天办公网

①统计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所示。

Excel文本函数LEN/LENB用法和实例教程-天天办公网

图5

公式解析

Excel文本函数LEN/LENB用法和实例教程-天天办公网

①统计B2单元格中字符串是否是15位。

②提取B2单元格字符串的第15位。

③ 判断步骤②中提取的值是否能被2整除,如果能整除返回性别“女”,不能整除返回性别“男”。

④ 如果B2单元格中字符串不是15位,则提取B2单元格字符串的第17位。

⑤ 判断步骤④中提取的值是否能被2整除,如果能整除返回性别“女”,不能整除返回性别“男”。

LENB函数

函数功能

LENB函数用于返回文本字符串中用于代表字符的字节数。

函数语法

LENB(text)

参数解释

text:要查找其长度的文本。空格将作为字符进行计数。

实例1 返回文本字符串的字节数

➊ 选中B2单元格,在公式编辑栏中输入公式:

=LENB(A2)

按“Enter”键即可返回A2单元格中文本字符串的字节数。

➋ 将光标移到B2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可返回其他单元格文本所对应的字节数,如图6所示。

Excel文本函数LEN/LENB用法和实例教程-天天办公网

图6