第7章
与字符串长度有关的函数应用

本章将介绍关于字符串长度的处理。

7.1　LEN与LENB函数的语法

长度函数包括两个：一个是计算字符数的LEN函数，另一个是计算字节数的LENB函数。这两个函数的语法如下。

计算字符数：

计算字节数：

这个B是英文Byte的缩写，表示字节。下面采用通俗易懂的方法解释字节。

每一个字，都占用1个字符的长度，这个字可以是中文、数字或英文。

中文和中文标点，每个字符都占用2个字节的长度；而数字、英文及英文的标点，每个字符占用1个字节的长度。

简单记忆就是：每个中文字占2个字节，每个数字和英文占1个字节。

7.2　计算字符串的长度

下面通过一个简单的示例来直观地认识字符串长度。

D12单元格的公式“=LEN（C12）”，结果为11。因为“ExcelHome”一共9个字母，是9个字符长度，“论坛”共2个汉字，是2个字符长度，加在一起恰好11。

E12单元格的公式“=LENB（C12）”，结果为13。“ExcelHome”一共9个字母，是9个字节，“论坛”共2个汉字，是4个字节长度，加在一起为13个字节。计算结果如图7-1所示。

7.3　案例：使用长度相减方法提取中英文字符

算出字符与字节的差异，有什么用呢？它可以用来分段提取中英文字符，如图7-2所示，根据C列的字符串，将中文与英文、数字分开。

我们首先分别计算字符与字节数，如图7-3所示。

H16单元格的公式“=LEN（C16）”，结果为11。

I16单元格的公式“=LENB（C16）”，结果为13。

计算这两个公式的差值，13-11等于2，恰好是C16单元格中的中文字符数。同样，第17行的13-9等于4，也恰好是C17单元格中的中文字符数。

这只是巧合吗？我们换一个生活中的场景模拟一下。例如，班里有a个胖子，b个瘦子，每一个胖子需要坐两个座位，每一个瘦子只需坐一个座位。你们班需要13个座位，总共11个人。那么分别有多少个胖子和多少个瘦子呢？

胖子的人数是不是用13减11就可以？有多少人本来就需要多少个座位，然而多出来的座位数就是每一个胖子多占的那一个，所以多占的座位就是胖子的总人数。

那么瘦子的人数呢？有了总人数11，减去胖子的人数2就等于瘦子的人数9。

根据以上情况，开始进一步写公式：

中文字符的长度：

英文、数字字符的长度：

下面结合提取函数将公式完善到表格中，如图7-4所示。

D16单元格的公式：

E16单元格的公式：

至此，分段提取完美解决了。

其他人提取英文时，公式可能为“=LEFT（C16，2*LEN（C16）-LENB（C16））”，与我们上面写得不太一样。其实两个公式的计算结果可以说完全一致，计算过程就是小学学过的加减法。LEN（C16）-（LENB（C16）-LEN（C16））相当于x-（y-x）=x-y+x=2x-y。

7.4　案例：使用通配符思路提取中英文字符

除了使用LEN与LENB的差值提取中英文字符外，还有没有其他的方法？

当然有，不过要记住一点，没有万能的方法，每一种方法都是根据数据特点选用的。如图7-5所示，这个案例与7.3节案例的区别在于中文字在前，英文、数字在后。可以使用之前的LEN与LENB的差值来处理，这里我们换一种思路。

认真观察，要提取的两段内容的分隔点在哪里？是不是第一个英文字母出现的位置？找到第一个英文字母，这个英文字母的位置之前就都是中文，后面则是英文。

如何知道第一个英文字母是什么？难道要把26个字母全都查找确认一遍？

能不能用一个字符把所有内容中的分隔点（字母或数字）都代表了呢？

这是个好思路！那什么字符可以代表全部内容呢？答案是通配符。

公式可用“=SEARCH（"?"，C21）”！

到这里公式就对了一半，SEARCH是查找字符的函数，中文和英文对于它来说，是没有差别的。既然查找英文，那也就是查找单字节的字符，就要用SEARCHB！如图7-6所示，在H21单元格输入以下公式并向下复制到H22单元格：

我们看到结果为9，这是因为SEARCHB是按照字节数查找，查找的目标值是“?”，即任意一个单字节字符。前面的中文“最棒论坛”，每一个字都是双字节，所以没办法与“?”匹配，从字母“E”开始才能匹配上。

另外注意，SEARCHB是按照字节数计算的，每一个中文字都有2个字节，前面4个汉字，总共8个字节，所以找到字母E时已经是第9个字节了。

同理，H22单元格的结果为5，是因为前面两个汉字占4个字节，字母L就是第5个字节。

找到分割点，开始提取吧，D21单元格的公式：

前面的9是按照字节计算得到的，然后使用LEFTB函数提取C21单元格的左侧9-1个字节长度。如果直接用LEFT来提取，结果就是“最棒论坛Exce”。

E21单元格的公式：

使用MIDB函数来提取第9个字节后的全部内容。

在D21单元格提取中文还可以使用公式“=LEFT（C21，（SEARCHB（"?"，C21）-1）/2）”。其中9-1得到中文字部分的长度，是按照字节计算出的8个字节，乘以2恰好是中文字的字符数。选择哪个公式，不仅与你的思路在哪里有关，而且与你的“武器库”是否丰富有关。

公式写错时，可以先看思路有没有问题，确定没问题就看公式的细节。

提示

在某些英文版的Office, LEN和LENB的结果是一样的，需要使用其他方法来提取字符。

7.5　案例：提取月份中的数字

字符与字节的差异，还有更多的用途。我们工作中经常遇到的一种情况就可以用得上，那就是提取月份的数字。如图7-7所示，将C列中的数字提取出来。

观察表格可以发现，这里面所有的数据都多了一个“月”字，把月删除就可以了。那么怎么删？可以使用公式“=SUBSTITUTE（C26，"月"，""）”。

公式比较简短，也很直观，那还有没有更简短的公式？必须有。首先还是观察数据特点，提取月份无非就是提取前面的数字。我们可以用公式“=LEFT（C26，1）”。这个公式适用于提取1～9月份，它们的数字都是只有一位，如果需要提取10、11、12月怎么办呢？可以再加一个IF函数判断字符串长度。整体思路没问题，就是有点烦琐。下面看看简单的做法。

它的含义是什么呢？对于10、11、12月，提取左边2个字节的长度，自然就是全部数字。对于1～9月，提取左边2个字节，首先会把第一个数字提取出来，然后在提取第2个字节时，由于只能提取“月”字的一半，所以只能提取一个显示不出来的空格。图7-8所示为提取出来的效果。

我们前面讲过，提取出来的文本型数字，不方便直接用于数学计算，所以将文本型数字转化为数值型数字，“减负”就可以了：

至此，完成了提取月份中数字的操作。

7.6　案例：删除字符串中最后一个“-”

将前文所学函数有机地结合起来，就可以处理很多复杂的数据工作。如图7-9所示，继续模拟公司编码模式，现在需要将编码中的最后一个“-”删除，从C列变成D列的样子。

我们先分析一下。通过前面所学知识可以了解，删除某个字符要用SUBSTITUTE函数。

删除其中第n个指定字符怎么操作？SUBSTITUTE的第4个参数使用数字n。

下面以C34单元格的“A-SW-0001-A”为例，数据中一共有3个“-”，而最后一个正好是第3个“-”。也就是说，不管数据中有几个横线，总数是多少，最后一个横线就在第几个。

现在的问题就是字符串里有几个横线，怎么数？

我们可以先计算原始的字符串长度，然后把横线删除，再计算一遍字符串长度，二者差值就是横线的总数。思路全部分析完了，开始写公式，如图7-10所示。

E34单元格的公式为“=LEN（C34）”，计算整体长度。

F34单元格的公式为“=SUBSTITUTE（C34，"-"，""）”，删除横线。

G34单元格的公式为“=LEN（F34）”，计算删除了横线的长度。

H34单元格的公式为“=E34-G34”，计算出横线的个数。

I34单元格的公式为“=SUBSTITUTE（C34，"-"，""，H34）”。

最后把整个思路组装成一个整体的公式，将每一个参数代入，最终D34单元格的公式：

7.7　案例：提取倒数第2个“-”之后的内容

下面继续学习一个有些难度的操作，如图7-11所示，现在需要提取倒数第2个“-”之后的内容。

同样，先确定思路，再一步步来做。

倒数第2个是正数第几个实际就是数数一共有几个“-”，然后减去1。就像站队时，一列有10个人，求倒数第2个同学是位于正数第几位？10-1=9。至于怎么算出一共几个横线，就是用我们刚刚讲过的方法：LEN-LEN（SUBSTITUTE）。

现在知道倒数第2个横线是正数第几个了，那么怎么计算这个横线在字符串中的位置呢？还记得FIND+SUBSTITUTE的方案吗？我们逐步来写公式，如图7-12所示。

E38单元格的公式“=LEN（C38）”，计算整体长度。

F38单元格的公式“=SUBSTITUTE（C38，"-"，""）”，删除横线。

G38单元格的公式“=LEN（F38）”，删除横线后的字符串长度。

H38单元格的公式“=E38-G38-1”，倒数第2个横线，正数是第几个。

I38单元格的公式“=SUBSTITUTE（C38，"-"，"@"，H38）”，将倒数第2个横线替换为任意一个不常见字符，如@。

J38单元格的公式“=FIND（"@"，I38）”，计算@在字符串中的位置。

K38单元格的公式“=MID（C38，J38+1，99）”，从@的后一位开始提取所有的字符串。

最后，将公式组装起来演示一遍，从K38单元格的公式“=MID（C38，J38+1，99）”入手，操作步骤如下。

（1）将辅助的J38替换掉，变为“=MID（C38，FIND（"@"，I38）+1，99）”。

（2）将I38替换掉，变为“=MID（C38，FIND（"@"，SUBSTITUTE（C38，"-"，"@"，H38））+1，99）”。

（3）修改未知数H38，变为“=MID（C38，FIND（"@"，SUBSTITUTE（C38，"-"，"@"，E38-G38-1））+1，99）”。

（4）同时处理E38和G38，变为“=MID（C38，FIND（"@"，SUBSTITUTE（C38，"-"，"@"，LEN（C38）-LEN（F38）-1））+1，99）”。

（5）辅助列还有F38，那就继续替换，公式变为：

至此，除了函数就只剩下引用的C38单元格，没有任何的辅助列。这是一个由87个字符5层嵌套组成的复杂公式。

嵌套公式并不可怕，只要思路正确，把每一步思路写出来，最后将公式组装到一起，就会很简单，自己动手来做一遍吧。 3MdTbgcLthyA41VuNHtmtbRLvt8Biw/g3KaychXZXSSPLfNaxDPOWsyMGq4Im5Kq