首页 > 编程笔记 > Office笔记 阅读:1

WPS TEXTBEFORE()函数的用法(附带实例)

TEXTBEFORE 函数可以返回指定分隔符之前的文本,函数语法格式如下:
=TEXTBEFORE(要拆分的文本, 分隔符, [搜索的方向], [是否区分大小写], [结尾匹配], [异常返回值])
参数说明如下:

参数 说明
参数1 要拆分的文本(必填项)
数值、字符串、数组、单元格区域
参数2 分隔符(必填项)
数值、字符串、数组、单元格区域
参数3 搜索的方向
正数、负数
省略参数时,默认值(1)
参数4 是否区分大小写
0 - 区分大小写
1 - 不区分大小写
省略参数时,默认值(0)
参数5 结尾匹配
0 - 不匹配结尾
1 - 匹配结尾
省略参数时,默认值(0)
参数6 异常返回值
省略参数时,默认值(#N/A)

【实例 1】提取日期。在 E5 单元格输入公式:
=TEXTBEFORE(B5:B10,"")
效果如下图所示:


图 1 提取日期

在实际工作中,一些软件系统导出的日期和时间是文本格式,当需要提取日期时,可以使用 TEXTBEFORE 函数。第 1 个参数引用 B5:B10 单元格区域,第 2 个参数使用空格作为分隔符,函数即可返回空格之前的日期。

【实例 2】提取日期,处理错误值 #N/A。在 E15 单元格输入公式:
=TEXTBEFORE(B15:B20,"")
效果如下图所示:


图 2 处理错误值#N/A

当第 1 个参数的字符串不包含第 2 个参数设置的分隔符时,函数会返回错误值 #N/A,可以通过设置函数的第 5 个参数或第 6 个参数来解决这个问题。

1) 设置 TEXTBEFORE 函数的第 5 个参数,在 E25 单元格输入公式:
=TEXTBEFORE(B25:B30,"",,,1)
效果如下图所示:


图 3 设置第5个参数

TEXTBEFORE 函数的第 5 个参数设置是否匹配结尾,从参数描述可能不是很好理解这个参数的作用,可以换个角度去理解这个参数,函数第 2 个参数设置分隔符,这个参数是接受数组的,可以指定多个分隔符,当第 5 个参数设置为 1(匹配结尾)时,函数会把第 1 个参数字符串的结尾也作为一个分隔符,这样即使字符串中没有包含第 2 个参数设置的分隔符,如 B25 单元格只有日期,没有时间,也没有空格,第 1 个参数字符串结尾也会作为分隔符,函数将返回第 1 个参数的字符串。

2) 设置 TEXTBEFORE 函数的第 6 个参数,在 E35 单元格输入公式:
=TEXTBEFORE(B35:B40,"",,,,B35:B40)
效果如下图所示:


图 4 设置第6个参数

TEXTBEFORE 函数的第 6 个参数设置异常返回值,当函数返回错误值 #N/A 时,可以通过设置此参数,将错误值转换为指定的返回值,参数引用 B35:B40 单元格区域,当 B35:B40 单元格区域不包含空格时,直接返回 B35:B40 单元格区域的值。

【实例 3】使用第 3 个参数提取省、市、区/县。在 F45 单元格输入公式:
=TEXTBEFORE(B45:B50,"-",3)
效果如下图所示:


图 5 使用第3个参数提取省、市、区/县

当字符串包含多个分隔符时,可以指定第 3 个参数搜索的方向,返回第 N 个分隔符之前的文本,第 3 个参数值设置为 3,可返回第 3 个“–”符号之前的所有文本。

【实例 4】使用第 3 个参数提取省、市、区/县(设置为负数)。在 F55 单元格输入公式:
TEXTBEFORE(B55:B60,"-",-1)
效果如下图所示:


图 6 使用第 3 个参数提取省、市、区/县(设置为负数)

当地址中没有区/县一级的地址时,导致字符串中包含的分隔符数量不同,TEXTBEFORE 函数的第 3 个参数搜索的方向就无法设置一个固定的正数数值了,此参数是接受负数的,当参数是负数时,函数会反方向搜索(从后向前),参数值设置为 –1,函数会返回最后一个分隔符之前的所有内容。

也可以这样理解,当 TEXTBEFORE 函数的第 3 个参数为负数时,函数可从后向前查找,删除第 N 个分隔符之后的内容。

【实例 5】使用多个分隔符提取姓名。在 E65 单元格输入公式:
=TEXTBEFORE(B65:B70,CHAR(SEQUENCE(26,,65)),,1)
效果如下图所示:


图 7 使用多个分隔符提取姓名

CHAR(SEQUENCE(26,,65)) 可以返回 A~Z 共 26 个大写字母作为 TEXTBEFORE 函数的第 2 个参数的分隔符,第 4 个参数设置为 1(忽略大小写),函数即可返回字母之前的文本。

相关文章