
第3章 文本函数
3.1 文本查找、提取的实例
函数1:MID函数(从任意位置提取指定数目的字符)
函数功能
MID函数用于返回文本字符串中从指定位置开始的特定数目的字符,该数目由参数指定。
函数语法
MID(text, start_num, num_chars)
参数解释
- text:必需。表示包含要提取字符的文本字符串。
- start_num:必需。表示文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1,以此类推。
- num_chars:必需。表示指定希望MID从文本中返回字符的个数。
用法剖析

实例解析
实例102 从规格数据中提取部分数据

表格的规格数据包含产品的厚度信息(见图3-1),现在需要要将厚度数据批量地提取出来。

图3-1
在原B列前插入一个新列用于显示提取的“厚度”数据,选中B2单元格,在公式编辑栏中输入公式:

按Enter键得出结果。
选中B2单元格,拖动右下角的填充柄向下复制公式,即可从A列数据中批量得出产品的厚度数据,如图3-2所示。

图3-2
公式解析
=MID(A2,9,3)
A2为目标单元格,即从A2单元格中的字符串提取,从第9位开始提取,并提取3位字符。
实例103 根据身份证号码快速计算年龄

表格中显示了员工的身份证号码,要求根据身份证号码快速计算出员工的年龄。要完成这项计算,需要先从身份证号码中提取出生年份,然后计算当前年份与出生年份的差值即为年龄。
选中C2单元格,在公式编辑栏中输入公式:

按Enter键计算出第一位员工的年龄。
选中C2单元格,拖动右下角的填充柄向下复制公式,即可根据B列中的身份证号码快速计算各自的年龄,如图3-3所示。

图3-3
公式解析

① 先使用TODAY函数返回当前日期,再使用YEAR函数返回当前日期中的年份值。
② 从B2单元格的第7位开始共提取4个字符,即提取的是出生年份。
③ 用当前的年份减去出生年份即为年龄。
实例104 从身份证号码中提取性别

身份证号码中包含有持证人的性别信息,即第17位如果是奇数,性别为“男”,如果是偶数,性别为“女”。可以使用MID函数实现提取然后再配合MOD函与IF函数实现判断。
选中C2单元格,在公式编辑栏中输入公式:

按Enter键即可从身份证号码中获取第一位员工的性别信息。
将鼠标指针指向C2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可从员工身份证号码中获取所有员工的性别信息,如图3-4所示。

图3-4
嵌套函数
MOD函数属于数学函数类型,用于求两个数值相除后的余数。
公式解析

① 提取B2单元格字符串的第17位。
② 计算步骤①中提取的值与2相除的余数。
③ 如果②步结果等于“1”(表示不能整除),则返回性别“男”,否则返回性别“女”。
实例105 提取产品的货号

表格中提供了产品的完整编码,完整编码中间部分为产品的货号(见图3-5 B列所示),现在要将货号单独提取出来。

图3-5
选中D2单元格,在公式编辑栏中输入公式:

按Enter键得出提取结果。
选中D2单元格,拖动右下角的填充柄向下复制公式,即可批量提取货号,如图3-6所示。

图3-6
嵌套函数
FIND函数属于文本函数类型。它用于在第二个文本串中定位第一个文本串,并返回第一个文本串的起始位置的值。这个函数在本小节中即将做出介绍。
公式解析

① 在B2单元格中找“-”符号所在位置。
② 在B2单元格中提取字符,提取的起始位置为①步返回值加1,提取的总位数为5。
提示
与MID用法类似的还有MIDB。MIDB函数根据指定的字节数,返回文本字符串中从指定位置开始的特定数目的字符。因此MID是按字符数计算的,而MIDB是按字节数计算的。一个字符等于两个字节。
函数2:LEFT函数(按指定字符数从最左侧提取字符串)
函数功能
LEFT函数用于从文本左侧开始提取指定个数的字符。
函数语法
LEFT(text, [num_chars])
参数解释
- text:必需。表示包含要提取的字符的文本字符串。
- num_chars:可选。指定要由LEFT提取的字符的数量。
用法剖析

实例解析
实例106 提取分部名称

如果要提取的字符串在左侧,并且要提取的字符宽度一致,可以直接使用LEFT函数提取。
选中D2单元格,在公式编辑栏中输入公式(见图3-7):

按Enter键即可提取B2单元格中字符串的前5个字符。
选中D2单元格,拖动右下角的填充柄向下复制公式,可以实现批量提取,如图3-7所示。

图3-7
实例107 从商品全称中提取产地信息

表格的B列中显示了特产的名称(见图3-8),要求从特产名称中提取该特产的产地信息。

图3-8
选中C2单元格,在公式编辑栏中输入公式:

按Enter键得出提取结果。
选中C2单元格,拖动右下角的填充柄向下复制公式,即可批量得出其他各特产的产地信息,如图3-9所示。

图3-9
嵌套函数
FIND函数属于文本函数类型。它用于在第二个文本串中定位第一个文本串,并返回第一个文本串的起始位置的值。这个函数即将在下面内容中介绍。
公式解析

① 在B2单元格中寻找空格,并返回其位置。
② 从B2单元格的最左侧开始提取,提取数量为①步返回值减去1,即提取空格前的字符串。
实例108 根据商品的名称进行一次性调价

表格中统计了公司各种产品的价格,需要将打印机的价格都上调200元,其他产品统一上调100元。
选中D2单元格,在公式编辑栏中输入公式(见图3-10):

按Enter键即可判断A1单元格中的产品名称是否是打印机,然后按指定规则进行调价,如图3-10所示。

图3-10
选中D2单元格,拖动右下角的填充柄向下复制公式,可以实现批量判断并进行调价,如图3-11所示。

图3-11
公式解析

① 从A2单元格的左侧提取,共提取3个字符。
② 如果①步返回结果是TRUE,返回“C2+200”;否则返回“C2+100”。
实例109 统计出各个地区分公司的参会人数

A列为所属地区和公司名称,并使用了“-”符号将地区和分公司相连接,B列为参加会议的人数统计。利用LEFT函数可以统计出各个地区分公司参加会议的总人数。
选中E2单元格,在公式编辑栏中输入公式:

按Ctrl+Shift+Enter组合键即可统计出“安徽”地区的参会人数,如图3-12所示。

图3-12
选中E2单元格,拖动右下角的填充柄至E4单元格,可以统计出“上海”和“北京”的参会人数。例如选中E3单元格,其公式为:

如图3-13所示。

图3-13
公式解析

① 使用LEFT函数依次提取A2∶A8单元格区域的前两个字符,并判断它们是否为D2中指定的“安徽”(当公式向下复制时则依次判断是否等于“上海”和“北京”),如果是则返回TRUE,否则返回FALSE。返回的是一个数组。
② 将步骤①数组中TRUE值对应在B2∶B8单元格区域中的数值取出,然后对取出的值进行求和运算。
提示
与LEFT用法类似的还有LEFTB。LEFTB函数是从文本左侧开始提取指定个数的字节。因此LEFT是按字符数计算的,而LEFTB是按字节数计算的。
函数3:RIGHT函数(按指定字符数从最右侧提取字符串)
函数功能
RIGHT函数根据所指定的字符数返回文本字符串中最后一个或多个字符。
函数语法
RIGHT(text,[num_chars])
参数解释
- text:必需。表示包含要提取字符的文本字符串。
- num_chars:可选。指定了要由RIGHT提取的字符的数量。
实例解析
实例110 提取商品的产地

如果要提取字符串在右侧,并且要提取的字符宽度一致,可以直接使用RIGHT函数提取。例如在下面的表格要从商品全称中提取产地。
选中D2单元格,在公式编辑栏中输入公式(见图3-14):

按Enter键即可提取B2单元格中字符串的最后4个字符,即产地信息。
选中D2单元格,拖动右下角的填充柄向下复制公式,可以实现批量提取,如图3-14所示。

图3-14
实例111 从字符串中提取金额数据

如果要提取的字符串虽然是从最右侧开始,但长度不一,则无法直接使用RIGHT函数提取,此时需要配合其他的函数来确定提取的长度。如图3-15所示表格中,由于“燃油附加费”填写方式不规则,导致无法计算总费用,此时可以使用RIGHT函数实现对燃油附加费金额的提取,然后再进行计算。

图3-15
选中D2单元格,在公式编辑栏中输入公式(见图3-16):

按Enter键即可提取C2单元格中金额数据,并实现总费用的计算。
选中D2单元格,拖动右下角的填充柄向下复制公式,可以实现批量提取,如图3-16所示。

图3-16
公式解析

① 使用LEN函数求取C2单元格中字符串的总长度,减5处理是因为“燃油附加费”共5个字符,减去后的值为去除“燃油附加费”文字后剩下的字符数。
② 从C2单元格中字符串的最右侧开始提取,提取的字符数是①步返回结果。
提示
与RIGHT用法类似的还有RIGHTB。RIGHTB函数是从文本右侧开始提取指定个数的字节。因此RIGHT是按字符数计算的,而RIGHTB是按字节数计算的。
函数4:SEARCH函数(查找字符串的起始位置)
函数功能
SEARCH函数可在第二个文本字符串中查找第一个文本字符串,并返回第一个文本字符串的起始位置的编号,该编号从第二个文本字符串的第一个字符算起。
函数语法
SEARCH(find_text,within_text,[start_num])
参数解释
- find_text:必需。表示要查找的文本。
- within_text:必需。表示要在其中搜索find_text参数的值的文本。
- start_num:可选。表示within_text参数中开始搜索的字符编号。
用法剖析

提示
SEARCH和FIND函数的区别主要有两点:
1. FIND函数区分大小写,而SEARCH函数不区分。
2. SEARCH函数支持通配符,而FIND函数不支持。例如公式“=SEARCH("VO?",A2)”,返回的是以由“VO”开头的三个字符组成的字符串第一次出现的位置。
实例解析
实例112 从货品名称中提取品牌名称

在本例A列中显示的是完整的货品名称,货品名称中包含有品牌信息,但每个品牌的字数不完全一样并且以空格与后面的文字间隔(见图3-17),如果想要从货品名称中提取品牌信息,可以配合使用SEARCH、LIFT两个函数来设置公式。

图3-17
选中C2单元格,在公式编辑栏中输入公式:

按Enter键即可提取A2单元格中货品名称中的品牌名称。
将鼠标指针指向C2单元格的右下角,待光标变成十字形状后,按住鼠标左键向下拖动进行公式填充,即可快速从其他货品名称中提取品牌名称,如图3-18所示。

图3-18
公式解析

① 在A2单元格中查找空格的位置,并返回位置值。
② 从A2单元格的最左侧开始提取,但取位数为①步返回值减1,即提取空格前的字符。
提示
与SEARCH用法类似的还有SEARCHB。SEARCHB函数查找时是按字节进行计算的。