2.2.2 灵活的文本处理函数
文本处理函数用来对文本字符串进行处理，此类函数可以帮助用户对文本字符串进行一系列嵌套处理，最终获取想要的字符。常见的文本处理函数包括LEN、LENB、LEFT、LEFTB、RIGHT、RIGHTB、MID、MIDB、UPPER、LOWER、SEARCH、SEARCHB、FIND、FINDB、REPLACE、REPLACEB、SUBSTITUTE、SUBSTITUTEB、TRIM、CONCATENATE、EXACT等。工作中经常用到的文本处理函数场景包括截取字符串中的部分字符串、拼接多个字符串生成一个字符串、查找字符串在另一个字符串中的位置、替换字符串中的旧字符串为新字符串。下面通过实例分别对常用的文本处理函数进行举例说明。
1.LEN、LENB函数
功能说明：
● LEN返回文本字符串中的字符个数。
● LENB返回文本字符串中用于代表字符的字节数。
语法：
● LEN(text)
● LENB(text)
参数：text必需。要查找其字符个数或字节数的文本。
示例：统计单元格A2内字符串“上海A广州B”里面的中文字符个数与英文字符个数。
公式与步骤：
● 中文字符个数：单元格E2内输入公式“=LENB(A2) -LEN(A2)”。
● 英文字符个数：单元格E3内输入公式“=2*LEN(A2) - LENB(A2)”，结果如图2-35所示。
图2-35 统计字符串的中文个数与英文个数
提示：
● LEN函数统计的是字符的个数，相当于1*中文字符个数+1*英文字符个数（或数字个数）。LENB函数统计的是字节数，相当于2*中文字符个数+1*英文字符个数（或数字个数）。
● 这两个函数可能并不适用于所有语言。
● 只有在将DBCS语言设置为默认语言时，函数LENB才会将每个中文字符按两个字节计数。否则，函数LENB与LEN相同，即将每个字符按1个字节计数。支持DBCS的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。
2.LEFT函数
功能说明：LEFT从文本字符串的第一个字符开始返回指定个数的字符。
语法：LEFT(text,[num_chars])
参数：
● text必需。要提取字符的文本字符串。
● num_chars可选。LEFT函数指定提取的字符数量。如果省略，默认为1。
示例：截取单元格A2内字符串“上海A广州B”的前3个字符。
公式与步骤：单元格E2内输入公式“=LEFT(A2,3)”，结果如图2-36所示。
图2-36 截取字符串前3个字符
3.RIGHT函数
功能说明：RIGHT根据所指定的字符数返回文本字符串中最后一个或多个字符。
语法：RIGHT(text,[num_chars]
参数：
● text必需。要提取字符的文本字符串。
● num_chars可选。RIGHT函数指定提取的字符数量。如果省略，默认为1。
示例：截取单元格A2内字符串“上海A广州B”的最后3个字符。
公式与步骤：单元格E2内输入公式“=RIGHT(A2,3)”，结果如图2-37所示。
图2-37 截取字符串最后3个字符
4.MID函数
功能说明：MID返回文本字符串中从指定位置开始的特定数目的字符，数目由用户指定。
语法：MID(text,start_num,num_chars)
参数：
● text必需。要提取字符的文本字符串。
● start_num必需。文本中要提取字符串的起始位置。
● num_chars必需。指定MID函数在文本中截取的字符个数。
示例：截取单元格A2内字符串“上海A广州B”中“广州”这两个字符。
公式与步骤：单元格E2内输入公式“=MID(A2,4,2)”，结果如图2-38所示。
图2-38 截取字符串中的广州
5.UPPER、LOWER函数
功能说明：UPPER将文本转换为大写字母，LOWER将文本转换为小写字母。
语法：
● UPPER(text)
● LOWER(text)
参数：text必需。要转换为大写或小写字母的文本。
示例：分别将字符串“Data Analysis”中的文本转换为大写字母或小写字母。
公式与步骤：
● 转换为大写字母：单元格E2内输入公式“=UPPER(A2)”。
● 转换为小写字母：单元格E3内输入公式“=LOWER(A2)”，结果如图2-39所示。
图2-39 转换字符串大小写
6.FIND函数
功能说明：用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起。
语法：FIND(find_text,within_text,[start_num])
参数：
● find_text必需。要查找的文本。
● within_text必需。包含要查找文本的文本。
● start_num可选。指定开始进行查找的字符的位置。如果省略，默认为1。
示例：找出字符串“我Love数据Analysis”中“数据”、大写“L”、小写“l”的位置。
公式与步骤：
● 字符串“数据”的位置：单元格E2内输入公式“=FIND("数据",A2,1)”。
● 字符“L”的位置：单元格E3内输入公式“=FIND("L",A2,1)”。
● 字符“l”的位置：单元格E4内输入公式“=FIND("l",A2,1)”，结果如图2-40所示。
图2-40 FIND查找字段串位置
提示：
● FIND函数的最后一个参数start_num可以省略，如果省略，默认从第一个字符开始查找。
● FIND函数区分大小写，因此从第一个字符开始查找大写“L”的位置是2，从第一个字符开始查找小写“l”的位置是11。
● FIND函数中的find_text参数不能包含通配符（“?”或“*”）。
7.SEARCH函数
功能说明：SEARCH函数可在第二个文本字符串中查找第一个文本字符串，并返回第一个文本字符串的起始位置的编号，该编号从第二个文本字符串的第一个字符算起。
语法：SEARCH(find_text,within_text,[start_num])
参数：
● find_text必需。要查找的文本。
● within_text必需。包含要查找文本的文本。
● start_num可选。指定开始进行查找的字符的位置。如果省略，默认为1。
示例：找出字符串“我Love数据Analysis”中“数据”、大写“L”、小写“l”以及“*数据*”的位置。
公式与步骤：
● 字符串“数据”的位置：单元格E2内输入公式“=SEARCH("数据",A2,1)”。
● 字符大写“L”的位置：单元格E3内输入公式“=SEARCH("L",A2,1)”。
● 字符小写“l”的位置：单元格E4内输入公式“=SEARCH("l",A2,1)”。
● 字符串“＊数据＊”的位置：单元格E5内输入公式“=SEARCH("*数据*",A2,1)”，结果如图2-41所示。
图2-41 SEARCH查找字段串位置
提示：
● SEARCH函数不区分大小写，从第一个字符开始查找大写“L”的位置是2，从第一个字符开始查找小写“l”的位置也是2。
● SEARCH函数中的find_text参数可以包含通配符（“?”或“*”）。查找“*数据*”字符串的位置，由于通配符“*”匹配的是任意字符，因此“*数据*”匹配的是整个字符串“我Love数据Analysis”。返回的结果是从第一个字符开始查找，字符串“我Love数据Analysis”在字符串“我Love数据Analysis”里面的位置，就是查找字符串的首个字符“我”的位置，最终返回1。
8.SUBSTITUTE函数
功能说明：用于在某一文本字符串中替换指定的文本，把old_text替换成new_text。
语法：SUBSTITUTE(text,old_text,new_text,[instance_num])
参数：
● text必需。要替换其中字符的文本。
● old_text必需。要替换的文本。
● new_text必需。替换old_text的文本。
● instance_num可选。指定要用new_text替换old_text的事件。如果指定了instance_num，只有满足要求的old_text被替换。如果省略，文本中所有的old_text都会被替换为new_text。
示例：分别将字符串“#我爱数据#数据爱我#”中的第一个“#”替换为“@”、第二个“#”替换为“@”、所有的“#”替换为“@”、前两个“#”都替换为“@”。
公式与步骤：
● 替换第一个"#"为"@"：单元格D2内输入公式“=SUBSTITUTE(A2,"#","@",1)”。
● 替换第二个"#"为"@"：单元格D3内输入公式“=SUBSTITUTE(A2,"#","@",2)”。
● 替换所有的"#"为"@"：单元格D4内输入公式“=SUBSTITUTE(A2,"#","@")”。
● 替换前二个"#"为"@"：单元格D5内输入公式“=SUBSTITUTE(SUBSTITUTE(A2,"#","@",1),"#","@",1)”，结果如图2-42所示。
图2-42 SUBSTITUTE替换指定字符串
提示：
● SUBSTITUTE函数里面的参数instance_num可以省略，如果省略表示替换所有的old_text，如果instance_num=2，表示替换第二次出现的字符串。
● 如需分别替换字符串为其他不同字符串，可以使用SUBSTITUTE函数进行嵌套替换，每次替换第一次出现的字符串即可。
9.REPLACE函数
功能说明：根据指定字符数，REPLACE将部分文本字符串替换为不同的文本字符串。
语法：REPLACE(old_text,start_num, num_chars,new_text)
参数：
● old_text必需。要替换其中字符的文本。
● start_num必需。old_text中要替换为new_text的字符起始位置。
● num_chars必需。使用new_text来进行替换的字符数。
● new_text必需。替换old_text中字符的文本。
示例：
● 将字符串“#我爱数据#数据爱我#”中的“我爱数据”替换为“Data”。
● 将手机号码“13013013000”中间的五位数字替换为“*****”。
公式与步骤：
● 替换“我爱数据”为“Data”：单元格D2内输入公式“=REPLACE(A2,2,4,"Data")”。
● 替换“13013013000”中间5位为“＊＊＊＊＊”：单元格D3内输入公式“=REPLACE (A3,4,5,"*****")”，结果如图2-43所示。
图2-43 REPLACE替换字符串
提示：
● REPLACE函数与SUBSTITUTE函数的区别：REPLACE函数是指定起始位置和字符长度进行替换的；而SUBSTITUTE函数是将给定的原始字符串替换成新的字符串。
● REPLACE函数与上面提到的MID函数也有相似之处：MID函数是根据起始位置和字符长度来进行截取；而REPLACE函数除了截取之外，还要将截取的字符串替换掉。
10.CONCATENATE函数
功能说明：将两个或多个字符串连接为一个字符串。
语法：CONCATENATE(text1,[text2], …)
参数：
● text1必需。要连接的第一个字符串。
● text2, … 可选。要连接的其他字符串。
示例：将“我”“爱”“数据分析”这三个字符串合并成一个字符串。
公式与步骤：
● 方法一：单元格E2内输入公式“=CONCATENATE(A2,A3,A4)”。
● 方法二：单元格E3内输入公式“=A2&A3&A4”，结果如图2-44所示。提示：
图2-44 CONCATENATE合并字符串
● CONCATENATE函数的功能等价于字符“&”的功能，用“&”符号也可以将单元格A2、A3、A4的内容合并成一个字符串。
● 在很多情况下，使用“&”符号比使用CONCATENATE函数连接字符串简单高效。
11.EXACT函数
功能说明：比较两个文本字符串，如果它们完全相同，则返回TRUE，否则返回FALSE。
语法：EXACT(text1, text2)
参数：
● text1必需。要比较的第一个文本。
● text2必需。要比较的第二个文本。
示例：分别比较单元格A2与A3、A2与A4的字符串是否相同。
公式与步骤：
● 比较A2与A3是否相同：单元格E2内输入公式“=EXACT(A2,A3)”。
● 比较A2与A4是否相同：单元格E3内输入公式“=EXACT(A2,A4)”，结果如图2-45所示。
图2-45 EXACT比较字符串
提示：
● EXACT函数区分大小写。
● 符号“=”不能区分大小写。例如，公式“=("A"="a")”，结果返回TRUE，不是FALSE。
12.TRIM函数
功能说明：除了单词之间的单个空格之外，移除文本中的所有空格。语法：TRIM(text)
参数：text必需。要从中移除空格的文本。
示例：用TRIM去除字符串“ Data Analysis ”中的空格。
公式与步骤：单元格E2内输入公式“=TRIM(A2)”，结果如图2-46所示。
图2-46 TRIM去除空格
提示：
● 字符串前后两端的空格全部去除。
● 字符串中单词之间的空格保留一个。
以上是对文本处理函数使用的介绍，并通过示例对函数进行了功能讲解，下面的文本处理函数案例一～案例三是文本处理函数的应用扩展。对于同一个案例采用了多种方法来解决。
13.文本处理函数案例一
本示例将如图2-47所示的数据表中的文件名称按照符号“.”进行拆分，分别取左侧的文件名称（不含后缀）和右侧的文件后缀。
图2-47 文本处理函数案例一
公式与步骤：
● 截取符号“.”左侧（方法一）：单元格B3内输入公式“=LEFT(A3,FIND(".",A3)-1)”，然后向下拖拽复制公式。
● 截取符号“.”左侧（方法二）：单元格C3内输入公式“=MID(A3,1,FIND(".",A3)-1)”，然后向下拖拽复制公式。
● 截取符号“.”右侧（方法一）：单元格D3内输入公式“=RIGHT(A3,LEN(A3)-FIND(".",A3))”，然后向下拖拽复制公式。
● 截取符号“.”右侧（方法二）：单元格E3内输入公式“=MID(A3,FIND(".",A3)+1,100)”，然后向下拖拽复制公式，结果如图2-47所示。
提示：
● 符号“.”左侧字符串的长度等于符号“.”位置的值减去数值1。
● 符号“.”右侧字符串的长度等于字符串的字符个数减去符号“.”位置的值。
14.文本处理函数案例二
本示例要从一组杂乱的文本字符串中提取中文信息。城市代码“S上海4”中包含了中文、大写英文和数字，需要从中提取城市“上海”，数据如表2-3所示。
表2-3 城市代码与对应的城市
公式与步骤：
● 方法一：单元格E2内输入公式“=LEFT (SUBSTITUTE(A2,"S",""),LENB(A2)-LEN(A2))”，然后向下拖拽复制公式。
● 方法二：单元格F2内输入公式“=LEFT(MID(A2,2,100),LENB(A2)-LEN(A2))”，然后向下拖拽复制公式。
● 方法三：单元格G2里面输入中文字符串“上海”，然后选中区域G2:G5，同时按组合键〈Ctrl+E〉，完成快速填充功能，结果如图2-48所示。
图2-48 文本处理函数案例二
提示：
● 方法一用SUBSTITUTE函数将字符串里面的字符“S”替换成空，然后通过LEFT函数截取左边中文字符（中文字符的长度根据LENB和LEN函数的组合运算得出）。
● 方法二用MID函数截取字符“S”右侧的所有字符串，后面的嵌套处理与方法一相同。
● 方法三的快速填充功能（Ctrl+E）是根据提供的截取样式进行快速填充，用这个方法从字符串里截取中文效果非常好。
15.文本处理函数案例三
本示例：对一组字符串进行相关统计，字符串数据及对字符串的处理需求如表2-4所示。
表2-4 字符串与处理需求
公式与步骤：
1）计算包含字母a的字符串个数（不区分大小写）：
● 方法一：单元格D2内输入公式“=COUNTIFS(A:A,"*A*")”。
● 方法二：单元格E2内输入数组公式“{=SUM(IFERROR(SEARCH("*a*",A2:A6), 0))}”。
2）计算包含字母A的字符串个数（区分大小写）：单元格D3内输入数组公式“{=SUM (IF(IFERROR(FIND("A",A2:A6),0)＞=1,1,0))}”。
3）计算首字母是a的字符串个数（不区分大小写）：
● 方法一：单元格D4内输入数组公式“{=SUM((LEFT(A2:A6,1)="a")+0)}”。
● 方法二：单元格E4内输入数组公式“{=SUM(IFERROR(SEARCH("a",LEFT(A2:A6,1)),0))}”。
4）计算首字母是A的字符串个数（区分大小写）：
● 方法一：单元格D5内输入数组公式“{=SUM(EXACT(LEFT(A2:A6,1),"A")+0)}”。
● 方法二：单元格E5内输入数组公式“{=SUM(IFERROR(FIND("A",LEFT(A2:A6,1)), 0))}”，结果如图2-49所示。
图2-49 文本处理函数案例三
提示：
● FIND函数与EXACT函数区分英文字母大小写，如果统计大写或小写字母的个数，可以用这两个函数来进行统计。
● SEARCH函数不区分字母大小写，COUNTIFS函数也不区分字母大小写。
● 符号“=”不区分字母大小写。例如，公式“=("A"="a")”，结果返回TRUE。