下载掌阅APP，畅读海量书库
立即打开
本章将从替换和查找两个方面讲解字符串的进一步处理方法。
SUBSTITUTE函数为替换字符，REPLACE函数为替换位置。这两个函数的语法内容如下。
替换字符：
替换位置：
稍微观察下可知，函数的语法已简明扼要地写出来了每个参数的含义。
1.基础使用
下面看一下替换字符SUBSTITUTE函数的基础用法。
D9单元格的公式为“=SUBSTITUTE（C9，"-"，"@"）”，从基础语法中看到，第2个参数old_text表示旧文本，第3个参数new_text表示新文本。也就是说，把字符串中的旧文本全部换成新文本，于是就有了D9单元格中的效果，把所有的“-”全部换成了“@”，如图6-1所示。
图6-1 SUBSTITUTE函数
有时只想单纯地把其中某一种字符删除，而不是替换成其他字符，怎么办？把对应的字符换成文本空""（连着两个英文半角的双引号）就可以了，于是有D10单元格的公式“=SUBSTITUTE（C10，"-"，""）”。
这个函数还有第4个参数instance_num，下面通过一个实例来看看它的意义。C13单元格的内容为“ABACADAEAF”，D13单元格的公式为“=SUBSTITUTE（C13，"A"，"-"，3）”，如图6-2所示。第4个参数为数字3，它的结果为“ABAC-DAEAF”。这个公式是把字母“A”替换为字符“-”，但是此时并不是所有的字母A都替换了，只有第3个A被替换了。第4个参数instance_num的作用就是控制替换第n个。
进一步观察，C14单元格的内容为“ABaCADAEAF”，使用相同的公式“=SUBSTITUTE（C14，"A"，"-"，3）”进行操作，结果为“ABaCAD-EAF”。我们发现同样是替换第3个A，替换的只有大写字母，小写字母a却未被替换。所以，SUBSTITUTE函数是区分大小写的，替换内容必须与公式完全一致才能替换，效果如图6-2所示。
图6-2 SUBSTITUTE第4参数
接下来介绍替换位置的REPLACE函数，REPLACE可以翻译为“取代”，所以它所取代的就是某个位置，下面是REPLACE函数的基础用法。
F9单元格的公式为“=REPLACE（C9，5，1，"ABC"）”，对比基础的语法，第二个参数为start_num，即开始的位置；第三个参数为num_chars，指字符的长度；第四个参数为new_text，即被替换成新的文本。那么这个公式整体上怎样理解呢？将C9单元格的字符串，从第5位字符开始取1位字符长度，也就是把原字符串中的第二个短横线替换为“ABC”。
F10单元格的公式为“=REPLACE（C10，3，6，"%"）”，是指把C10单元格的字符串，从第3位开始取6个字符，也就是“-South”这一串字符替换为“%”，完成效果如图6-3所示。
图6-3 REPLACE函数基础用法
从这里可以看出，在替换时REPLACE只认识“位置”，把原来占该位置的内容全部删掉，然后把新字符串插入此处，替换前后的字符数并不要求长度完全一致。
2.案例：删除字符串中的数字或字母
C17单元格为字符串“0A12Bc345Def6”，可以看出，D17单元格的结果为“ABcDef”，所有数字都没了，只保留了字母，如图6-4所示，这是怎么做到的？
图6-4 替换所有数字
先把D17单元格的公式贴出来：
看到了什么？公式十层嵌套，公式太长看不懂！
从公式的结构看，公式内容几乎都是一样的。就是先把1替换为空，然后把2替换为空，3，4，……，9，0，可以说没有任何技术难度，除了写的时候会让人有些烦躁。接下来继续看一个更让人烦躁的公式。
如图6-5所示，D21单元格的结果为“0123456”，所有字母都没有了，只剩下了数字。结合上面的思路我们要怎么写公式呢？
图6-5 替换所有字母
逐一把英文字母替换，难道要写26层嵌套？不对！SUBSTITUTE函数是区分大小写的，算上小写，那一共要写52层嵌套……
思路完全正确，下面先把公式写出来：
公式虽然很长，但是仅用27层就够了。仔细看看其中的一个关键点——UPPER（C21），它表示什么意思？
我们通过Excel的帮助信息可以看到，UPPER是将文本转换为大写字母。于是，UPPER（C21）就把字符串从“0A12Bc345Def6”变为了“0A12BC345DEF6”，所有的小写字母都“长大了”，然后只替换大写的A～Z即可。
额外普及两个函数。
LOWER：将文本转换为小写字母，与UPPER完全相反。LOWER（C21）的结果为“0a12bc345def6”。
PROPER：文本字符串的首字母及文字中任何非字母字符之后的任何其他字母转换成大写，将其余字母转换为小写。这一段内容是Excel帮助中的原文，读起来有点绕，简单地说，PROPER就是把每个单词首字母大写，其他字母都小写。例如，“=PROPER（"this is a TITLE"）”的结果为：This Is A Title。
如果工作中要求替换字母或数字，可以将以上两个公式保存在计算机中，用的时候直接复制，更换其中所引用的单元格即可，不用从头开始写一遍，正所谓“前人栽树，后人乘凉”。
有的读者会嫌公式太长，技术含量低，不够帅气。但工作中应先以解决问题为基准，再考虑帅气。在论坛中，有很多高手写过这种提取字符的公式，几乎全都是晦涩难懂，充分体现了解决这种问题的难度。所以，更好的处理办法就是使用VBA。
后面会讲解一个自定义函数，专门对付这种混乱的字符提取问题。
Excel在2003版本中只能接受7层嵌套，所以想写7层以上的嵌套公式还需要借助定义名称，十分麻烦。在2007及以上版本可以接受64层嵌套，所以不是太过“奇葩”的编写，都不会超出可接受的嵌套层数。
3.案例：电话号码升位
在我国的座机历史中，多地都经历过电话号码升位。例如，将7位座机号的第2位后面增加一个数字“8”，升位为8位座机号，这要怎么做呢？
图6-6 电话号码升位
如图6-6所示，D25单元格的公式为“=REPLACE（C25，3，0，8）”，其中，第三个参数是0，表示从第3个字符开始，取0个字符长度，将其替换为数字8。这0个字符长度表示什么都没有，于是就成了在第3位插入一个数字“8”的效果。
FIND的含义是找到，SEARCH的含义是寻找，这两个函数的语法如下。
找到：
寻找：
这两个英文单词的意思差不多，而且函数的语法也基本一致。这里面翻译成“找到”和“寻找”，其细节之处还是有区别的，这会在后面章节进行讲解。
1.常规用法
如图6-7所示，这是FIND函数和SEARCH函数相同的基础用法。
图6-7 基础用法
以FIND函数为例，FIND（find_text, within_text，[start_num]），第1个参数find_text表示查找的文本。在哪里找呢？第2个参数within_text告诉我们在这里找。
D9单元格的公式为“=FIND（"天津"，C9）”，就是查找“天津”这两个字在C9单元格中的位置，于是找到了在第1位的天津，所以得到结果1。
F9单元格的公式为“=FIND（"河北"，C9）”，在C9单元格中“河北”两个字位于第4位。
下面看看SEARCH函数的公式。
D10单元格的公式为“=SEARCH（"市"，C10）”，在C10单元格中“市”字位于第3位。
F10单元格的公式为“=SEARCH（"玄武"，C10）”，结果是错误值“#VALUE！”。这是因为C10单元格的字符串是“北京市东城区”，这里并没有“玄武”二字，所以返回错误结果。
在这一部分常规查找中，FIND和SEARCH是完全相同的。
2.案例：以横线分段提取字符
在实际工作中，会遇到各种各样的特殊情况，那么怎样才能扩展一些思路呢？如图6-8所示，模拟的是有些公司编码的规则，每一部分代表一个层级，各层级之间使用横线连接，然后得到唯一的编码值。现在要提取第一个“-”和第二个“-”之间的部分，得到G14：G15单元格区域的结果。要怎么做呢？
图6-8 提高用法
接下来是思路拆解，既然要获取两横线之间的部分，那么先来找一找两个横线都在哪里？
对于A-SW-0001-A，首先在D14单元格编写公式“=FIND（"-"，C14）”，得到结果2，说明第一个“-”在字符串的第2位。那怎样查到第二个“-”的位置呢？
回归到基础语法可以发现，FIND有第三个参数“[start_num]”，意思是开始的数字，那它是不是表示从第几位开始找呢？假设是这样，我们在E14单元格输入公式“=FIND（"-"，C14，D14+1）”，看到结果为5。说明第二个“-”是在原字符串的第5位。D14单元格的结果是第一个“-”的位置，然后公式中的“D14+1”，也就是告诉FIND函数不用从头开始找了，在第一个“-”后面找就可以了，这样就能成功地跳过了第一个“-”，找到第二个“-”。
我们把D14：E14单元格区域的公式向下复制到D15：E15单元格区域，如图6-9所示，就确定了C15单元格字符串中“-”的位置。
图6-9 横线位置
那么用什么提取字符呢？用MID函数。这个函数我们在5.1节学过。MID从第一个横线的位置起始，然后长度为两个横线位置的差值，那么F14单元格的公式为“=MID（C14，D14，E14-D14）”，如图6-10所示。
图6-10 提取字符1
F14单元格得到的结果是“-SW”，前面多了一个“-”。这是因为从第一个“-”开始提取，所以就一起提取了。想去掉它很简单，把MID的第2个参数+1，即将公式改成“=MID（C14，D14+1，E14-D14）”，如图6-11所示。
图6-11 提取字符2
输入公式后，结果为“SW-”，后面又多了一个“-”进一步修正公式为“=MID（C14，D14+1，E14-D14-1）”，如图6-12所示。
图6-12 提取字符3
这样结果就完全正确了。我们是借用了两个辅助单元格D14和E14才完成F14单元格的公式，那么可不可以不用辅助单元格D14和E14，直接用一个公式搞定呢？
从F14单元格的结果入手，先把里面的“E14”全部改为E14单元格的公式，于是F14的公式整合为：
接下来进一步整合D14单元格的公式：
如图6-13所示，这里只剩下引用了原始数据的C14单元格。删除辅助列，只保留最终的整合公式列，结果完全正确。
图6-13 提取字符整合公式
一开始就写这种长长的公式，一定会有很多不理解地方。但结合前面讲的分步操作，将公式整合到一起，就容易多了。很多公式都是这样一步步打磨出来的。
3.结合替换函数
刚才通过一步步的演示，提取出来第一个“-”和第二个“-”之间的内容。其中确定第二个“-”位置时，借助了先找出的第一个“-”的位置的方法。那有没有方法直接找到第二个“-”呢？
如图6-14所示，以“*”为分隔符，使用公式将C列的数字分别提取到D、E、F列中。
图6-14 结合替换函数
我们在5.1节讲LEFT、RIGHT、MID函数时，已经熟悉了这三个函数的用法：先数每一段的起始点、长度，然后分别提取。那有没有办法使用函数直接计算每一个“*”的位置呢？
方法仍然是先确定思路。C列的数据特点是“数字*数字*数字”的结构，那只需确定每一个“*”的位置。怎么确定呢？
第一个“*”很简单，公式为“=FIND（"*"，C19）”，然后得到数字3，说明C19单元格中的“*”是位于第3位的。
怎么确定第二个“*”的位置呢？此时就要回忆一下什么函数能帮我们找到第二“*”。SUBSTITUTE函数！我们利用SUBSTITUTE函数的特点，把C19单元格中的第二个“*”换成任意一个不常见字符，如“@”。于是公式写为：
得到的结果为12*3456@789。
这时看到第二个“*”已经被“@”代替，现在来查找“@”的位置，也就是原来第二个“*”的位置。那就使用FIND在SUBSTITUTE中查找吧。
如图6-15所示，我们已经通过上面的公式确定了两个“*”的位置，现在开始分段提取数据。
图6-15 确定“*”位置
D19单元格提取数字1的公式：
E19单元格提取数字2的公式：
F19单元格提取数字3的公式：
首先，看数字1和数字2，这两个很简单。这里最需要注意的一点是“+1”与“-1”，因为找到的是“*”的位置，而我们在提取数字时，这个星号是不需要的，所以要对提取的起始点、提取的长度稍加修正。再次提示，不要死记硬背，可以参考刚刚讲过的提取横线之间的字符的公式调整。先把公式写出来，再对细节慢慢修改。
然后，看数字3，这里没使用RIGHT，大家可以动手练练用RIGHT函数提取，用总字符长度减去第二个“*”的位置，就能确定长度，这里着重讲使用MID函数。
利用MID函数的公式写法是目前比较流行的，主要原因是公式较短。首先通过MID确定提取的起点H19+1。那么要提取多长呢？上面的公式中写了一个99，因为从第二个“*”之后是一整段数字，不管有多长我全要了，所以只要写一个“足够大”的数字就可以。数字足够大能保证提取字符的最大通用性。
这里并不是只能写99，也可以写其他数字。比如这里可以写9，变成“=MID（C19，H19+1，9）”，因为最后一段数字，长度没有超过9位。而我写99更具有通用性，比如字符串“12*345*6789012345”，最后一段有10位的长度，“MID（C19，H19+1，9）”的结果就会为“678901234”，少提取一个数字5。
那为什么不写100呢？Excel高手们有一种潜规则，就是比谁的字符短，谁的水平就更高。99是2个字符长度，100则是3个字符长度。
那为什么不写15、28、72等等这些呢？因为它们与99都是2个字符，为了保持可以提取字符的最大通用性，在公式字符数相同的时候，一般会选择最大的数值。
前面我们写的公式，都是借助了G、H的辅助单元格，我们可以动手把这些辅助的内容嵌入公式中，形成一个长长的嵌套公式。
D19单元格公式：
E19单元格公式：
F19单元格公式：
将D19：F19单元格区域的公式向下复制到D21：F21单元格区域，图6-16所示就是最终公式合成的结果。
图6-16 公式合成
E19单元格中复杂的长公式，你是不是也写出来了呢？
4.案例：提取指定的第n段字符串
可能会有人问，这些在工作中能用到吗？
我们所讲的某些工具、思路的使用要看两个方面：一方面需要你有“土壤”，即公司中有类似的情况需要你处理；另一方面就是当遇到可以用的场景时，你的“武器库”中是否保存了相应的“武器”？
我们来看一个案例，以一家公司的产品编码为例，第一段是产品大类，第二段是大类下的小类，第三段是具体产品名称编码，第四段是产品第几代，第五段是其他信息。每一段之间用“-”连接，这样就生成了一个含有充足信息的唯一值。
图6-17 所示为模拟的编码规则，第四段、五段信息不是每个产品都必须有的。现在的需求是提取具体产品名称段的编码，也就是第二个“-”之后的信息。
图6-17 提取指定位置字符串
还是先想思路：先找到第二个“-”的位置，然后从它后面提取全部字符。再详细一点，怎么找“-”的位置？用FIND+SUBSTITUTE的思路，我在此一步步演示给大家，如图6-18所示。
第一步，替换第二个“-”为特殊字符“@”，E25单元格公式为：
第二步，查找特殊字符“@”的位置，F25单元格公式为：
第三步，提取字符串，G25单元格公式为：
图6-18 提取指定字符串步骤
下面我们将公式合成，把E25、F25单元格的公式合入G25单元格中，合成公式为：
5.二者差异
前面讲了很多思路，相信大家对FIND、SEARCH的用法有了一些了解，现在要说说FIND和SEARCH之间的差异。简单来说，FIND区分大小写，不支持通配符，SEARCH不区分大小写，支持通配符。
常见的通配符有两个：*和?。
*：代表任意n个字符（n≥0）
?：代表任意1个字符（问号必须是英文状态下的半角问号“?”，不能是中文状态下的全角问号“？”）
除常见的这两个外，还有一个通配符～（键盘上数字1左边的按键）。它的作用是使通配符失去通配性，变成普通字符。
下面通过一些实例加深理解，如图6-19所示，以字符串“Excelhome”为例，两个函数写下相似的公式，但是得到不同的结果。
图6-19 二者差异
D35单元格的公式：
因为FIND要区分大小写，所以执行命令时会忽略大写字母E，得出小写的e在第4位。
F35单元格公式：
SEARCH不区分大小写，所以不管是大写还是小写，有E就行，于是结果为1。
D36单元格公式：
FIND要查找的就是e*h这三个字符，而在“Excelhome”中并没有，所以得到错误值。
D37单元格公式：
FIND同样找的是e?h这三个字符，根本就没有，所以还是错误值。
F36单元格公式：
SEARCH查找的是以字母e开头，以h结尾，中间n个字符的内容，那么在“Excelhome”中，可以发现“Excelh”这一段就完全符合要求，它位于整个字符串的第1位，于是结果为1。
F37单元格公式：
SEARCH仍然查找的是以字母e开头，以h结尾，中间只相隔1个字符的内容，观察后发现“elh”符合要求，它位于第4位，所以结果为4。
FIND就像一个刚直不阿的“判官”，丁是丁，卯是卯。
SEARCH就像一个“和事佬”，差不多就行了。
你记住了吗？