Excel文本函数——LEN/LENB函数详解

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