在Excel中处理字符串

L罗乐 2019-02-17

展开全文

本文转载自公众号：XL，作者：XL小李。

首先，大家新年好啊，给大家拜个晚年，祝大家猪年顺利！

又是偷懒的一段时间，最近一些事情又起了反复，但好在已经解决，不论是否圆满，都应该往前看了，毕竟人生嘛，难免有遗憾。

本文从后台收到的一个问题入手，来分析Excel中如何对字符串进行处理。会涉及大量的文本函数、分列功能、VBA中的Split函数。

主要内容一览

本期主要包括以下三个方面的内容：

引例：分析问题→发现问题中的规律性→选择合适的处理方法（多尝试）
与字符串相关的函数和运算：
1. &连接符；
2. 文本函数：left、mid、right、len(b)、find、substitute；
分列功能
VBA中的Split函数

一、引例

大约1个月以前，有位朋友在后台问了我这样一个问题，当时我跟她说：用现有函数实现的可能性非常小，应该需要用到VBA，之后我就没管这事，自己玩儿去了。

前天，她又给我发了这样的消息，说实话，很佩服她这个写函数的水平，这个函数嵌套不一层一层慢慢拆、慢慢组合肯定是看不懂的。但是，就她所描述的这个问题，显然还是VBA更加便捷，也更容易达到结果。

经过分析可以发现，她提出的这个问题是有规律可循的。这一长串字符是其实是以“,”来分隔的，很容易就会联想到Excel中的分列功能（其实也就是VBA中作用于字符串的split函数）。

当然，用分列功能并不太容易解决这个问题，在第三部分会详细分析。经过对问题的分析，发现了这个问题的一些规律性，最终采用VBA的方式来处理该问题。这是一般的问题处理逻辑，应该是简单明了的。

按照她的要求，我设计了三个字符串，算是一个简单的例子，具体见下图。

二、与字符串相关的函数和运算

我并不想将本文的主题仅仅限定于介绍一个例子，我还有更大的目标，那就是盘点一下如何在Excel中处理字符串。

需要说明的是，本文所讨论的字符串并不单指传统意义上由英文字母、汉字、数字组成的字符串，如abc、浙江省、a2c等，还包括纯数字组成的数据，比如123456。

与其说是在讨论字符串，不如说是在讨论一串由英文、中文、数字、符号组成的数据。为了方便起见，统称为字符串。

注：数值型数字与文本型数字其实是不同的，这在数据类型相关的文章中有介绍过，在过段时间要发布的那篇《如何系统地学习Excel》一文中也会涉及。

2.1 &连接符

在Excel中，运算符除了普通的数学运算符之外，还包括一个特殊的运算符：连接符&。这个符号的作用就是将符号前后两个部分的内容连接起来，在处理函数相关问题的时候可以经常碰到这个运算符。

比如 '浙江'&“省”的结果就是'浙江省'，而 2&3 的结果是23。

就跟这张图一样。

2.2 文本函数

对于一串数据而言，我们可以进行的操作大概有：

1. 数一下多长，即字符串长度，len和lenb函数；

2. 截取字符串的一部分，left、mid和right函数；

3. 看看字符串里面是否包含某个特定的字符或字符串/找出某个特定字符或字符串在另一个字符串中的起始位置，find和search函数；

4. 将原字符串中的一部分替换为其他字符串，substitute函数。

简单解释一下：

1. len函数和lenb函数的唯一区别在于，lenb将一个汉字的长度当作是2，其实这个b的含义就是byte，毕竟汉字占两个字节，也可以称作双字节byte形式；

2. 三个截取函数：left函数是从左往右截，mid函数是在中间截一部分，right函数是从右往左截。这三个函数也有双字节byte形式，即leftb、midb和rightb，与lenb类似。

以上提及的len、left、mid和right函数在《函数系列：基础函数-2 文本函数》一文中早就介绍过了，不了解的，可以再看看。其中，left、mid、right函数在利用身份证号提取信息的例子中有非常关键的作用。

3. find和search函数的作用基本是一样的，返回的结果都是一串字符在另一串字符中的起始位置，当找不到时，返回错误值#VALUE!。基于这个特性，就可以嵌套if、iserror、iferror等函数，来判断字符串里面是否包含某个特定的字符或字符串。具体公式参见附件。

4. 替换函数substitute的语法是这样的：

SUBSTITUTE(text, old_text,

new_text, [instance_num])

考虑将字符串'abc123'中的'123'这一部分替换为'd'这个例子，最后的结果是'abcd'。

（1）第一个参数是最初的整体字符串，也就是'abc123”；

（2）第二个参数时要被替换掉的部分，也就是'123'；

（3）第三个参数是替换成的部分，也就是'd'；

（4）第四个参数是可选参数，也就是替换掉第几个'123”，如果原字符串中有好几个'123'，可能就需要进行这个操作。

2.3 其他

其实，还有istext、isnumber、text、value等函数与此相关，本文不多描述，大家可以自行了解。

三、分列

分列这个功能，我在《分列删除重复项冻结窗格（t21）》一文中提到过。这个功能的作用是将单列文本拆分成多列。处理各个部分之间有相同的分隔符的文本时，大概率会用到分列功能。

具体到引例中的例子，这些字符串的分隔符都是逗号，采用分列功能可以得到这样的结果。

由于每串字符串中逗号的个数不一样，那么采用分列功能最后生成的结果个数也不同，每一行所使用的单元格个数是不同的。

分列完之后，我们需要找到每一行中最右边那个带有#号的单元格，然后再对该单元格内的数据进行截取，才能达到结果。

显然这需要进行多次判断，涉及到循环，肯定会用到数组函数，但是说来惭愧，这个函数我思考了很久也没写出来。

尽管分列功能并没有帮助引例的最终解决，但是这个功能还是十分重要的，工作中会用到也是很正常的，希望大家能够了解一二。

四、VBA-Split函数

采用VBA之后，就是编程的范畴了。

先来分析一下如何处理这个问题，我的分析思路是这样的：

1. 读取数据，可以采用sub过程 selection.value形式，也可以采用function过程将之作为自定义函数参数的形式；

2. 数据读取到内存中之后，是一个字符串。然后把一个字符串分成好几个字符串，这个时候用到的是VBA中的Split函数，分隔符就是逗号，返回的结果是一个数组。其实，如果大家对Python略有了解的话，这就是对字符串进行了一次切片；再者，上文提到的分列功能其实就是封装了Split函数遍历数组写入单元格等一系列操作。我们可以这样理解VBA：Excel所有的现成功能，其实都是封装好的VBA。毕竟office就是建立在VB语言上的。

3. 对数组的每一个元素进行遍历，判断是否带有#号，将带有#号的挑选出来，放在另一个数组arr内；

4. 对数组arr的最后一个元素再次进行Split操作，此时的分隔符变成了井号，得到一个只有两个元素的新数组，这两个元素分别对应着我们要提取的两个数据。接下来只要将这个元素写入单元格或者输出到函数即可。

分析完之后，对程序进行调试，这时候建议大家先用sub过程，sub过程调试完了之后，再封装带有两个参数的function过程中。

最后的程序是这样的：

Function chs(rng0 As Range, b As Boolean) '本例恰好是两种情况，所以第二个参数设置为布尔型正合适，TRUE、FALSE分别对应不同的情况

Dim strs, str, arr(1 To 10), a, chs0, ch, rng '先将数组arr定义得稍微大一点，其实并没有那个多带#号的，太小会下标越界；后面使用redim的话又可能出错
rng = rng0.Value '读取字符串
strs = Split(rng, ',') 'Split函数分隔，返回一个数组
a = 1

For Each str In strs
If str Like '*#*' Then 'Like运算用来判断是否包含井号，也可以用InStr(str,'#')来达到一样的效果
arr(a) = str
a = a 1
End If
Next

ch = arr(a - 1) '因为for循环有a = a 1语句，所以最后一个井号应该是第a-1个元素
chs0 = Split(ch, '#')

If b = False Then
chs = chs0(0) '当第二个参数为FALSE的时候，返回前半部分
Else: chs = chs0(1) '当第二个参数为TRUE的时候，返回后半部分
End If

End Function