常用的文本函数

常用的文本函数
SIMPLE HEADLINE

Excel 中定义的“文本”，除了汉字和字母，还可以是文本型数字和空文本等。用于文本处理的函数主要包括 MID 函数、LEFT 函数、RIGHT 函数、TEXT 函数、FIND 函数和SUBSTITUTE 函数等。

格式化文本
格式化文本

使用自定义数字格式功能，可以将单元格中的内容显示为自定义的格式，TEXT 函数也具有类似的功能。

示例 5-44 从身份证号码中提取出生日期

图5-66 展示了某乡镇居民建立档案信息表的部分内容，需要根据 E 列的居民身份证号码（本书中的身份证号码均为虚拟，仅为举例之用），提取出该居民的出生日期。
图5-66从身份证号码中提取出生日期
将 F2 单元格数据格式设置为短日期格式，然后输入以下公式，将公式向下复制填充到数据表的最后一行。
=TEXT(MID(E2,7,8),'0-00-00')*1
TEXT 函数可以通过指定的格式代码对数字应用特定格式，进而更改数字的显示方式，函数语法如下。
TEXT(value,format_text)
第一参数是要转换格式的数字。第二参数是指定的格式代码。TEXT 函数所使用的格式代码与当前操作系统中 Excel 的单元格数字格式代码基本相同，但是不能使用有填充效果或是颜色类的格式代码。
设置单元格的格式仅仅是数字显示外观的改变，其实质仍然是数值本身。而 TEXT 函数将数值转换为带格式的内容后，其实质已经是文本，不再具有数值的特性。
本例公式中的“MID(E2,7,8)”部分，用 MID 函数从 E2 单元格的第 7 位开始，提取出 8个字符，结果为“19910307”。
TEXT 函数使用格式代码“0-00-00”，将“19910307”转换为具有日期样式的字符串“1991-03-07”。此时的字符串仅具有了日期样式，还不是真正的日期，最后使用 *1 的方法，将其转换为日期序列值。
TEXT 函数不仅能对数字应用特定格式，还能对数字或公式结果完成简单的条件判断。
示例 5-45 计算员工入职年限
图 5-67 所示，是某公司员工信息表的部分内容，需要根据 I 列的入职日期，判断员工的入职年限。入职年限的划分依据为 5 年及以上、3 年及以上和 3 年以下，统计截止日期为2019 年 12 月 31 日。
图5-67员工信息表
K2 单元格输入以下公式，将公式向下复制填充到数据表最后一行。=TEXT(DATEDIF(I2,'2019-1-1','y'),'[ ＞ =5]5 年及以上 ;[ ＞ =3]3 年及以上 ;3 年以下 ')
完整的格式代码分为 4 个区段，各区段之间以半角分号间隔，四个区段的定义为“大于0; 小于 0; 等于 0; 文本”。
如果省略部分区段，条件含义也会发生相应变化。如果使用三个条件区段，其含义为“大于 0; 小于 0; 等于 0”。如果使用两个条件区段，其含义为“大于等于 0; 小于 0”。使用一个条件区段时，则将格式应用于所有字符。
TEXT 函数的第二参数中，允许使用自定义的条件判断设置。
四个区段时可以表示为“[条件1]结果1;[条件2]结果2;不符合条件的其他部分;文本”。
三个区段时可以表示为“[ 条件 1] 结果 1;[ 条件 2] 结果 2; 不符合条件的其他部分”。
两个区段时可以表示为“[ 条件 ] 结果 ; 不符合条件的其他部分”。本例公式中的“DATEDIF(I2,'2019-1-1','y')”部分，先使用 DATEDIF 函数以 I2 单元格的入职日期为开始日期，以“2019-1-1”为结束日期，计算出员工入职的整年数。
再使用 TEXT 函数三个区段的格式代码“[ ＞ =5]5 年及以上 ;[ ＞ =3]3 年及以上 ;3 年以下”，对 DATEDIF 函数的计算结果进行判断。符合第一个条件＞ =5 时返回“5 年及以上”，符合第二个条件＞ =3 时返回“3 年及以上”，不符合条件的其他部分返回“3 年以下”。
字符提取与合并

ENJOY THE SUMMER
在一些有规律的字符串中提取字符或是将多个字符根据条件进行合并，是数据处理过程中的一项基础性的操作，用于字符提取与合并的函数主要有 MID 函数、RIGHT 函数和 LEFT函数等。
示例 5-46 分别提取中文物料名称和英文数字规格型号
图5-68 展示了某公司物料明细表的部分内容，A 列是物料名称和规格型号的混合内容，需要在 B 列和 C 列分别提取出物料名称和规格型号。
图5-68物料明细表
B2 单元格输入以下公式，提取出物料名称，将公式向下复制填充到数据表最后一行。=LEFT(A2,LENB(A2)-LEN(A2))
C2 单元格输入以下公式，提取出规格型号，将公式向下复制填充到数据表最后一行。=RIGHT(A2,LEN(A2)*2-LENB(A2))
LEN函数和LENB函数用于统计字符长度。LEN函数对任意单个字符都按一个长度计算。LENB 函数则将任意单个的单字节字符按一个长度计算，将任意单个的双字节字符按两个长度计算。
双字节字符又称为全角字符，是指一个字符占用两个标准字符位置的字符，所有中文字符均为双字节字符。半角字符是指一个字符占用一个标准字符位置的字符，又称为单字节字符。在英文输入法状态下输入的字符，默认就是半角字符。
LEFT 函数和 RIGHT 函数的作用是从字符串的左 / 右侧开始，提取指定数量的字符，函数语法如下。
LEFT(text,[num_chars])
RIGHT(text,[num_chars])
第一参数是要提取的字符串或单元格引用，第二参数指定要提取几个字符，如果省略第二参数时，会默认提取最左 / 右侧的一个字符。
字符串的提取，先要观察数据的分布规律。本例中的数据都是以中文的全角字符开头，随后是字母或是数字等半角字符，提取之前，需要先确定好全角字符和半角字符的个数。
已知一个全角字符的字节数等于两个半角字符的字节数，因此全角字符个数 = 字节数 -符数。
半角字符个数 = 字符数 - 全角字符个数，也就是 = 字符数 -( 字节数 - 字符数 )，或者写成 = 字符数 + 字符数 - 字节数。继续修约，则半角字符个数的公式变成 = 字符数 *2- 字节数。
B2 单元格公式中先使用“LENB(A2)-LEN(A2)”得到全角字符的个数。再使用 LEFT 函数从 A2 单元格的左侧开始，根据计算出的全角字符个数，提取出该单元格中的物料名称。
C2 单元格中的公式使用“LEN(A2)*2-LENB(A2)”得到半角字符的个数，再使用 RIGHT函数从 A2 单元格右侧开始，根据计算出的半角字符个数，提取出该单元格中的规格型号。
注意：本例的计算方法仅适用于中文操作系统下的中文版 Excel。
在一些有固定间隔符号的字符中提取数字时，可以借助 FIND 函数完成。
示例 5-47 从文本中提取商品零售价数字
图 5-69 展示了某保健品经销商进货明细表的部分内容，E 列的零售价是数字和文字的混合内容，需要从中提取出零售价数字。
图5-69提取零售价
F2 单元格输入以下公式，将公式向下复制到数据表的最后一行。
=LEFT(E2,FIND('/',E2)-1)*1
观察 E 列的数据可以发现，零售价金额都是在最左侧，和后面的单位之间以“/”作为间隔符号。因此只要确定“/”的位置，然后提取出“/”左侧的数字即可。
FIND 函数能够根据指定的字符串，在另一个单元格或字符串中定位这个字符串首次出现的位置。函数语法如下。
FIND(fifind_text,within_text,[start_num])
第一参数是要查找的字符。第二参数用于指定要在哪些单元格或字符串中进行查找。第三参数是可选参数，用于指定从第二参数的第几个字符位置处开始查找。省略该参数时，默认为 1。无论第三参数是否为 1，FIND 函数最终返回的位置都是以第二参数的首个字符开始计算。
如果第二参数中不包含要查找的内容，结果将返回错误值 #VALUE!。
本例中，FIND 函数以“/”作为查找内容，在 E2 单元格中查找“/”首次出现的位置，返回结果为 4。
再将这个结果减去 1，作为 LEFT 函数的第二参数，最终提取出首个“/”之前的数字。
由于 LEFT 函数、RIGHT 函数等文本类函数的提取结果都是文本型内容，因此最后用乘1 的方式，将文本型数字转换为数值如果要检测字符中是否包含并返回某些关键字，可以使用 FIND 函数结合 LOOKUP 函数完成。
示例 5-48 根据关键字匹配判断物料所属大类
图 5-70 展示了某公司物料盘点表的部分内容，需要根据 B 列物料名称中包含的关键字，以 H 列的对照表作为参照，判断物料所属大类。
图5-70物料盘点表
F2 单元格输入以下公式，将公式向下复制填充到数据表最后一行。=LOOKUP(1,0/FIND(H$2:H$5,B2),H$2:H$5)
公式中的“FIND(H$2:H$5,B2)”部分，分别查找 H$2:H$5 单元格区域中每个关键词在B2 单元格中首次出现的位置。如果 B2 单元格中包含其中的某个关键词，结果返回表示位置的数字，否则返回错误值。
{5;#VALUE!;#VALUE!;#VALUE!}
然后使用 0 除以该内存数组，使其变成由 0 和错误值构成的新内存数组。
{0;#VALUE!;#VALUE!;#VALUE!}
最后使用 LOOKUP 函数，以数值 1 作为查询值在内存数组中查找。由于找不到 1，因此以小于 1 的最接近值 0 进行匹配，并返回第三参数 H$2:H$5 单元格区域中对应位置的内容“PP”。
要按照分类对指定内容进行合并时，可以使用 TEXTJOIN 函数完成。
示例 5-49 合并同一分店的发票号码
图 5-71 展示了某公司下属分店的部分销售开票记录，需要按照不同的分店将发票号码合并到同一个单元格。
图5-71销售开票记录
步 骤 1复制 H 列的分店名称，粘贴到 K 列，然后单击 K 列数据区域的任意单元格（如K2），切换到【数据】选项卡下，单击【删除重复值】按钮。在弹出的【删除重复值】对话框中保留默认设置，单击【确定】按钮，在弹出的提示对话框中再次单击【确定】按钮，完成不重复分店名称的提取，如图 5-72 所示。
步 骤 2在 L1 单元格输入字段标题“发票号码”，然后在 L2 单元格输入以下数组公式，按＜ Ctrl+ Shift+Enter ＞组合键，再将公式复制填充到数据表最后一行，如图 5-73所示。{=TEXTJOIN('、',TRUE,IF(H$2:H$184=K2,A$2:A$184,''))}
图5-72删除重复值
图5-73合并同一分店的发票号码
TEXTJOIN 函数的作用是使用指定的分隔符号，连接多个单元格或多个字符串。函数语法如下。
TEXTJOIN(delimiter,ignore_empty,text1,[text2],…)
第一参数是指定的分隔符类型。第二参数是逻辑值，用于指定是否忽略连接内容中的空单元格或空文本，选择 1 或 TURE 为忽略，选择 0 或 FALSE 为不忽略。第三参数是要连接的单元格区域或是数组。
本例中的“IF(H$2:H$184=K2,A$2:A$184,'')”部分，如果 K2 单元格的分店名称等于H 列的分店名称，IF 函数返回 A$2:A$184 单元格区域中对应的发票号码，否则返回空文本 ''。得到内存数组结果为：
{15005006;'';'';'';'';'';'';'';……;'';'';'';'';'';''}
再使用 TEXTJOIN 函数连接该内存数组中的各个元素，第一参数为指定的间隔符号“、”，第二参数使用逻辑值 TRUE，表示忽略内存数组中的空文本，最终完成同一分店下所有发票号码的连接。
字符替换

除了使用查找替换功能来批量替换字符，还可以使用替换类函数将字符串中的部分或全部内容替换成新的字符串。

示例 5-50 使用替换法提取科目代码和科目名称

如图 5-74 所示，A 列是一些从系统导出的内容，同一个单元格内包含有科目代码和各级科目名称，不同项目之间使用“/”间隔，需要将这些内容分别拆分到右侧各列中。

图5-74提取科目代码和科目名称
B2 单元格输入以下公式，将公式复制填充到 B2:E25 单元格区域。
=TRIM(MID(SUBSTITUTE($A2,'/',REPT('',99)),COLUMN(A1)*99-98,99))
公式中用到了多个文本类函数的组合。
REPT 函数的作用是根据指定的次数重复显示字符。
TRIM 函数的作用是清除字符中的多余空格。
COLUMN 函数的作用是返回参数的列号，如果省略参数，则返回公式所在单元格的列号。本例中的“COLUMN(A1)*99-98”部分，先使用 COLUMN 函数返回 A1 单元格的列号1，再用 COLUMN 函数的结果乘以 99 减 98，即 1*99-98，结果仍然是 1。当公式向右复制时，COLUMN 函数会依次得到 B1、C1……的列号，再将这些列号乘以 99 减 98，即相当于 2*99-98、3*99-98……，最终得到按 99 递增的序号 1、100、199……，以此作为 MID函数的第二参数。
SUBSTITUTE 函数的作用是将字符串中的指定字符替换为新的字符，函数语法如下。SUBSTITUTE(text,old_text,new_text,[instance_num])
第一参数是需要替换字符的文本或单元格引用。
第二参数指定要替换哪些字符。
第三参数指定要将原有字符替换成什么内容，如该参数为空文本或仅保留参数之前的逗号时，相当于将需要替换的字符删除。
第四参数是可选参数，当第一参数中包含有多个要替换的字符时，该参数指定要替换第几个。省略该参数时，则表示全部替换。
公式中的“SUBSTITUTE($A2,'/',REPT(' ',99))”部分，先使用“REPT(' ',99)”将空格重复 99 次，最终得到 99 个空格。再使用 SUBSTITUTE 函数将 A2 单元格中的每一个分隔符“/”都替换为 99 个空格，使其变成以下样式的新字符串。
'119301           往来中转                  通赔'
接下来使用 MID 函数，从 SUBSTITUTE 函数返回的字符串中提取字符，提取的起始位置是“COLUMN(A1)*99-98”得到的序号 1，提取长度为 99 个字符，结果如下。
'119301 '
最后使用 TRIM 函数清除字符串中的多余空格，得到科目代码“119301”。
当公式向右复制时，MID 函数分别从 SUBSTITUTE 函数返回字符串中的第 1 位、第100 位、第 199 位……依次提取出 99 个字符，并使用 TRIM 函数清除多余空格，最终得到不同的科目代码和科目名称。
提示：公式中的 99 可以是其他一个较大的数字，目的是增加原有字符串中各个科目之间的间隔宽度，以便于 MID 函数分段截取出带空格的字符。
END
赞 (0)

相关推荐