Excel Len函数LenB函数区别及使用方法的7个实例,含仅截取数字、字母或汉字及返回指定词的长度

网友投稿 4860 2022-06-08

在 Excel 中,Len函数和LenB函数都用于返回指定文本的长度。Len函数返回字符数,它把全角(如“汉字”)和半角(如“数字和字母”)字符都计作一个字符;LenB函数返回字节数,它把全角字符计作 2 个字节、半角字符计作 1 个字节。

Len函数和LenB函数常与 Left、Mid、Right、Substitute 等函数组合使用,例如 Left + Len + LenB 组合实现截取字符串左边的数字或字母,Right + Len + LenB 组合实现截取字符串右边的汉字,Len + Substitute 组合实现统计一个单元格中的文本指定词组的个数。

 

一、Excel Len函数和LenB函数的语法

1、 Len函数表达式:LEN(Text)

中文表达式:LEN(文本)

 

2、 LenB函数表达式:LENB(Text)

中文表达式:LENB(文本)

 

3、说明:

A、Len函数和LenB函数都返回指定文本的长度,但它们有区别:Len函数返回指定文本的字符数,它将每个半角字符(如“数字或字母”)和全角字符(如“汉字”)都计作一个字节;LenB函数返回指定文本的字节数,它将半角字符计作一个字节、全角字符计作两个字节。

B、Len函数和LenB函数将空格也计作字符进行统计,但空文本(如 ""),它们都忽略。

 

 

二、Excel Len函数的使用方法及实例

(一)返回空格和空文本的长度实例

1、双击 A1 单元格,输入公式 =LEN(" "),按回车,返回 1;双击 A1,把双引号之间的空格删除,按回车,返回 0;再次双击 A1,把双引号 "" 改为 B1,按回车,同样返回 0;操作过程步骤,如图1所示:

图1

2、公式说明:

A、公式 =LEN(" ") 返回空格 " " 的长度,结果为 1,说明Len函数将空格的长度计为 1。

B、公式 =LEN("") 和 =LEN(B1) 返回空文本 "" 和空单元格的长度,结果为 0,说明Len函数忽略空文本和空单元的长度。

 

(二)返回半角和全角字符的长度实例

1、双击 B1 单元格,输入公式 =LEN(A1),按回车,返回 15;操作过程步骤,如图2所示:

图2

2、公式说明:

公式 =LEN(A1) 返回 A1 中文本的长度,A1 的文本由半角字符(即“空格、字母和数字”)和全角字符(即“汉字”)组成,它们加起来共 15 个,说明Len函数把半角和全角字符都计作一个字符。

 

 

三、Excel LenB函数的使用方法及实例

(一)返回星号 * 和问号 ? 的长度实例

1、双击 B1 单元格,把公式 =LENB(A1) 复制到 B1,按回车,返回 5;操作过程步骤,如图3所示:

图3

2、A1 中有两个星号 * 与 两个问号 ?,星号是不区分半角与全角的,问号区分半角与全角,因此“*?*?”的长度为 5。

 

(二)返回全角字符与半角字符的长度实例

1、双击 B1 单元格,把公式 =LENB(A1) 复制到 B1,按回车,返回 10;双击 B2,输入公式 =LENB(A2),按回车,返回 19;操作过程步骤,如图4所示:

图4

2、A1 中的 Excel 2016 有 5 个字母、4 个数字和 1 个空格,加起来共 10 个;A2 中除有 A1 的内容外,还有 1 个空格和 4 个汉字,长度为 19,说明每个汉字,LenB函数计作 2 个字节,数字、字母和空格计作一个字节。

 

 

四、Excel Len函数和LenB函数的应用实例

(一)Left + Len + LenB函数组合实现截取文本中的字母和数字

1、假如要截取“Excel 2016 函数教程”中的字母和数字。双击 B2 单元格,把公式 =LEFT(A2,LEN(A2)-(LENB(A2)-LEN(A2))-1) 复制到 B2,按回车,返回“Excel 2016”;操作过程步骤,如图5所示:

图5

 

2、公式 =LEFT(A2,LEN(A2)-(LENB(A2)-LEN(A2))-1) 说明:

A、LEN(A2) 用于返回 A2 的字符个数,结果为 15;LENB(A2) 用于返回 A2 的字节个数,结果为 19。

B、(LENB(A2)-LEN(A2)) 用于计算有多少个汉字,用字节数 19 减字符数 15 的结果 4 恰好是汉字总数;这是利用了LenB函数把每个汉字计作两个字节,Len函数把所有字符计作一个字符。

C、LEN(A2)-(LENB(A2)-LEN(A2))-1 用于计算要截取字符串的长度,用总字符数 15 减掉 4 个汉字,结果恰好剩下字母、数字和空格数,减 1 是把 2016 后的空格去掉。

D、则公式变为 =LEFT(A2,10),最后用Left函数从 A2 中左边第一个字符开始截取,共截取 10 字符,恰好截取得 Excel 2016。

 

Excel Len函数和LenB函数的区别及使用方法的7个实例,含仅截取数字、字母或汉字及返回指定词的长度

(二)Right + Len + LenB函数组合实现截取汉字

1、同样以截取“Excel 2016 函数教程”的汉字为例。双击 B2 单元格,把公式 =RIGHT(A2,LENB(A2)-LEN(A2)) 复制到 B2,按回车,返回“函数教程”;操作过程步骤,如图6所示:

图6

 

2、公式 =RIGHT(A2,LENB(A2)-LEN(A2)) 说明:

A、LENB(A2)-LEN(A2) 用于计算 A2 中的汉字个数(上面已经解释过),结果为 4。

B、则公式变为 =RIGHT(A2,4),最后用 Right 函数从右边开始截取 4 个字符,结果恰好为四个汉字“函数教程”。

 

提示:以上截取数字、字母和汉字的方法只适用于数字和字母在左边、汉字在右边的情况,如果数字或字母分布在汉字两边(如“Excel 函数教程 2016”)将不能正确截取,此时,需用 Left + LookUp + LenB + Mid + Row 函数实现,具体请参考《Excel拆分单元格与用分列与公式把一列内容拆分为两列或三列》一文。

 

(三)Len + Subtitute函数组合实现返回文本中指定词组的个数

1、假如要统计一个单元格中某个词(如 excel)的个数。双击 A2 单元格,把公式 =(LEN(A1)-LEN(SUBSTITUTE(A1,"excel","")))/LEN("excel") 复制到 A2,按回车,返回 3;双击 A1,把 E 改 e,A2 中的值变为 4;操作过程步骤,如图7所示:

图7

 

2、公式 =(LEN(A1)-LEN(SUBSTITUTE(A1,"excel","")))/LEN("excel") 说明:

A、SUBSTITUTE(A1,"excel","") 用于用空值 "" 取代 A1 中所有 excel;LEN(SUBSTITUTE(A1,"excel","")) 用于返回 A1 中除全部excel 外字符串的长度,结果为 28。

B、LEN(A1) 用于返回 A1 的长度 43,则 LEN(A1)-LEN(SUBSTITUTE(A1,"excel","")) 变为 43 - 28,结果等于 15,也就是 A1 中所有excel 的全部字母的个数。

C、LEN("excel") 用于返回 excel 的字母个数 5,则公式变为 15/5,结果等于 3;值得注意的是,Substitute函数区分大小写,以大写E 开头的 Excel 并没有被取代,结果才统计出 3 个 excel,当把 E 改为 e 后,才统计出 4 个。

 

(四)LenB函数把每个汉字的长度计为1的问题

1、如果 Excel 的默认语言没有设置为“中文”,LenB函数把每个汉字的长度计为 1,要解决此问题,只需把 Excel 的默认语言设置为“中文”;演示如图8所示:

图8

2、把默认语言设置为“中文”后,一定要关闭所有打开的 Excel 窗口,然后重新打开,设置才生效。

 

(五)Len + if函数组合实现截取指定位数的数字

1、如果要求两位数字截取一位、四位截取两位、六位截取四位。双击 B1 单元格,把公式 =IF(LEN(A1)=2,LEFT(A1,1),IF(LEN(A1)=4,LEFT(A1,2),IF(LEN(A1)=6,LEFT(A1,4)))) 复制到 B1,按回车,返回 1;选中 B1,双击 B1 右下角的单元格填充柄,则返回剩余数字的截取结果;操作过程步骤,如图9所示:

图9

 

2、公式 =IF(LEN(A1)=2,LEFT(A1,1),IF(LEN(A1)=4,LEFT(A1,2),IF(LEN(A1)=6,LEFT(A1,4)))) 说明:

A、LEN(A1) 返回 A1 中数字的长度;LEN(A1)=2 是左起第一个 IF 条件,如果 LEN(A1) 等于 2,则执行 LEFT(A1,1),即用 Left 函数从左边截取一个数字;否则执行 IF(LEN(A1)=4,LEFT(A1,2),IF(LEN(A1)=6,LEFT(A1,4))。

B、继续往下执行,如果 LEN(A1)=4 成立,执行 LEFT(A1,2),即用 Left 函数从左边截取两位数字;否则执行 IF(LEN(A1)=6,LEFT(A1,4);最后一个 If 以此类推。

 

(六)Right + Len + Find函数组合实现从右边截取指定字符

1、如果要截取右边两个单词。双击 B1 单元格,把公式 =RIGHT(A1,LEN(A1)-FIND("fun",A1)+1) 复制到 B1,按回车,返回 function tutolail;操作过程步骤,如图10所示:

图10

 

2、公式 =RIGHT(A1,LEN(A1)-FIND("fun",A1)+1) 说明:

A、FIND("fun",A1) 用于返回要截取字符(function tutolail)的开始位置,结果为 12。

B、LEN(A1) 用于返回 A1 中文本的长度,结果为 28。LEN(A1)-FIND("fun",A1)+1 用于计算要截取字符的长度,结果为 28 - 12 + 1 = 17。

C、则公式变为 =RIGHT(A1,17),最后用 Right 函数从右边截取 17 个字符,恰好是 function tutolail。

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:Excel Right函数和RightB函数使用方法的7个实例,含与Len、Find组合截取长度不一指定字符后的数字
下一篇:Excel Search函数和SearchB函数模糊查找的用法12个实例,含与Mid、Sum、Index、Match和Count组合
相关文章