excel文本清洗相关函数

2021-03-15 01:40 阅读:2379

ASC(text)

对于双字节字符集 (DBCS) 语言,该函数将全角(双字节)字符转换成半角(单字节)字符。

  • Text    必需。 文本或对包含要更改文本的单元格的引用。 如果文本不包含任何全角字母,则不会对文本进行转换。

 

DBCS(text)

将字符串中的半角(单字节)字母转换为全角(双字节)字符。

  • Text    必需。 文本或包含要转换的文本的单元格的引用。 如果文本中不包含任何半角英文字母或片假名,则不会对文本进行转换。

 

CHAR(number)

返回对应于数字代码的字符。 使用 CHAR 可将从其他类型计算机上的文件中获取的代码页数字转换为字符。

  • Number    必需。 介于 1 到 255 之间的数字,指定所需的字符。 使用的是当前计算机字符集中的字符。

 

CODE(text)

返回文本字符串中第一个字符的数字代码。 返回的代码对应于本机所使用的字符集。

  • Text    必需。 要为其获取第一个字符的代码的文本。

 

UNICHAR(number)

返回给定数值引用的 Unicode 字符。

  • Number    必需。 Number 是表示字符的 Unicode 数字。

备注:

  • 返回的 Unicode 字符可以是一个字符串,比如以 UTF-8 或 UTF-16 编码的字符串。
  • 如果 Unicode 数字为部分代理项且数据类型无效,则 UNICHAR 返回错误值 #N/A。
  • 如果数字的数值超出允许范围,则 UNICHAR 返回错误值 #VALUE! 。
  • 如果数字为零 (0),则 UNICHAR 返回错误值 #VALUE! 。

 

UNICODE(text)

返回对应于文本的第一个字符的数字(代码点)。

  • text    必需。 文本是需要其 Unicode 值的字符。

 

CLEAN(text)

删除文本中所有不能打印的字符。 对从其他应用程序导入的文本使用 CLEAN,将删除其中含有的当前操作系统无法打印的字符。 例如,可以使用 CLEAN 删除某些通常出现在数据文件开头和结尾处且无法打印的低级计算机代码。

  • text    必需。 要从中删除非打印字符的任何工作表信息。

 

TRIM(text)

除了单词之间的单个空格之外,移除文本中的所有空格。 对于从另一个可能含有不规则间距的应用程序收到的文本,可以使用 TRIM。

  • Text    必需。 要从中移除空格的文本。

 

EXACT(text1, text2)

比较两个文本字符串,如果它们完全相同,则返回 TRUE,否则返回 FALSE。 函数 EXACT 区分大小写,但忽略格式上的差异。 使用 EXACT 可以检验在文档中输入的文本。

  • Text1    必需。 第一个文本字符串。
  • text2    必需。 第二个文本字符串。

 

LEN(text)

LENB(text)

返回文本字符串中的字符个数。只有在将 DBCS 语言设置为默认语言时,函数 LENB 才会将每个字符按 2 个字节计数。 否则,函数 LENB 的行为与 LEN 相同,即将每个字符按 1 个字节计数。

  • Text    必需。 要查找其长度的文本。 空格将作为字符进行计数。

 

FIND(find_text, within_text, [start_num])

FINDB(find_text, within_text, [start_num])

函数 FIND 和 FINDB 用于在第二个文本串中定位第一个文本串,并返回第一个文本串的起始位置的值,该值从第二个文本串的第一个字符算起。

无论默认语言设置如何,函数 FIND 始终将每个字符(不管是单字节还是双字节)按 1 计数。

当启用支持 DBCS 的语言的编辑并将其设置为默认语言时,FINDB 会将每个双字节字符按 2 计数。 否则,FINDB 会将每个字符按 1 计数。

  • find_text    必需。 要查找的文本。
  • within_text    必需。 包含要查找文本的文本。
  • start_num    可选。 指定开始进行查找的字符。 within_text 中的首字符是编号为 1 的字符。 如果省略 start_num,则假定其值为 1。

备注:

  • FIND 和 FINDB 区分大小写,并且不允许使用通配符。 如果您不希望执行区分大小写的搜索或使用通配符,则可以使用 SEARCH 和 SEARCHB 函数。
  • 如果 find_text 为空文本 (""),则 FIND 会匹配搜索字符串中的首字符(即编号为 start_num 或 1 的字符)。
  • Find_text 不能包含任何通配符。
  • 如果find_text不存在于within_text,FIND 和 FINDB 将返回#VALUE! 错误值。
  • 如果start_num不大于零,则 FIND 和 FINDB 返回#VALUE! 错误值。
  • 如果start_num大于最大within_text,则 FIND 和 FINDB 返回#VALUE! 错误值。
  • 可以使用 start_num 来跳过指定数目的字符。 以 FIND 为例,假设要处理文本字符串“AYF0093.YoungMensApparel”。 若要在文本字符串的说明部分中查找第一个“Y”的编号,请将 start_num 设置为 8,这样就不会搜索文本的序列号部分。 FIND 从第 8 个字符开始查找,在下一个字符处找到 find_text,然后返回其编号 9。 FIND 始终返回从 within_text 的起始位置计算的字符编号,如果 start_num 大于 1,则会对跳过的字符计数。

 

SEARCH(find_text,within_text,[start_num])

SEARCHB(find_text,within_text,[start_num])

在第二个文本字符串中查找第一个文本字符串,并返回第一个文本字符串的起始位置的编号,该编号从第二个文本字符串的第一个字符算起。

  • find_text    必需。 要查找的文本。
  • within_text    必需。 要在其中搜索 find_text 参数的值的文本。
  • start_num    可选。 within_text 参数中从之开始搜索的字符编号。

备注:

  • SEARCH 和 SEARCHB 函数不区分大小写。 如果要执行区分大小写的搜索,可以使用 FIND 和 FINDB 函数。
  • 可以在 find_text 参数中使用通配符 (问号 (?) 和星号 (*)) 。 问号匹配任意单个字符;星号匹配任意一串字符。 如果要查找实际的问号或星号,请在字符前键入波形符 (~)。
  • 如果找不到 find_text 值,则返回#VALUE! 错误值。
  • 如果省略了 start_num 参数,则假设其值为 1。
  • 如果 start_num 不大于 0 (零) 或大于 within_text 参数的长度 ,则 #VALUE! 错误值。
  • 可以使用 start_num 来跳过指定数目的字符。 以 SEARCH 函数为例,假设要处理文本字符串“AYF0093.YoungMensApparel”。 若要在文本字符串的说明部分中查找第一个“Y”的位置,请将 start_num 设置为 8,这样就不会搜索文本的序列号部分(即本例中的“AYF0093”)。 SEARCH 函数从第 8 个字符开始,在下一个字符处查找在 find_text 参数中指定的字符,并返回数字 9。 SEARCH 函数总是返回从 within_text 参数的起始位置计算的字符的编号,如果 start_num 参数大于 1,则会计算跳过的字符。

 

LEFT(text, [num_chars])

LEFTB(text, [num_bytes])

LEFT 从文本字符串的第一个字符开始返回指定个数的字符。只有在将 DBCS 语言设置为默认语言时,函数 LEFTB 才会将每个字符按 2 个字节计数。 否则,函数 LEFTB 的行为与 LEFT 相同,即将每个字符按 1 个字节计数。

  • Text    必需。 包含要提取的字符的文本字符串。
  • num_chars    可选。 指定要由 LEFT 提取的字符的数量。
    • Num_chars 必须大于或等于零。
    • 如果 num_chars 大于文本长度,则 LEFT 返回全部文本。
    • 如果省略 num_chars,则假定其值为 1。
  • Num_bytes    可选。 按字节指定要由 LEFTB 提取的字符的数量。

 

RIGHT(text,[num_chars])

RIGHTB(text,[num_bytes])

根据所指定的字符数返回文本字符串中最后一个或多个字符。

  • text    必需。 包含要提取字符的文本字符串。
  • num_chars    可选。 指定希望 RIGHT 提取的字符数。
    • Num_chars 必须大于或等于零。
    • 如果 num_chars 大于文本长度,则 RIGHT 返回所有文本。
    • 如果省略 num_chars,则假定其值为 1。
  • Num_bytes    可选。 按字节指定要由 RIGHTB 提取的字符的数量。
    • Num_bytes必须大于或等于零。
    • 如果num_bytes大于文本长度,则 RIGHT 返回所有文本。
    • 如果num_bytes,则假定其为 1。

 

MID(text, start_num, num_chars)

MIDB(text, start_num, num_bytes)

返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。无论默认语言设置如何,函数 MID 始终将每个字符(不管是单字节还是双字节)按 1 计数。当启用支持 DBCS 的语言的编辑并将其设置为默认语言时,函数 MIDB 会将每个双字节字符按 2 计数。 否则,MIDB 会将每个字符按 1 计数。

  • text    必需。 包含要提取字符的文本字符串。
  • start_num    必需。 文本中要提取的第一个字符的位置。 文本中第一个字符的 start_num 为 1,以此类推。
  • 如果start_num大于文本长度,则 MID/MIDB 返回空文本 (") 。
  • 如果start_num小于文本长度,但 start_num 加 num_chars 超过文本长度,则 MID/MIDB 返回直到文本末尾的字符。
  • 如果start_num小于 1,则 MID/MIDB 返回#VALUE! 错误值。
  • num_chars    MID 必需。 指定希望 MID 从文本中返回字符的个数。
  • 如果num_chars为负数,则 MID 返回#VALUE! 错误值。
  • Num_bytes    MIDB 必需。 指定希望 MIDB 从文本中返回字符的个数(字节数)。
  • 如果num_bytes为负数,则 MIDB 返回#VALUE! 错误值。

 

 

LOWER(text)

将一个文本字符串中的所有大写字母转换为小写字母。

  • Text    必需。 要转换为小写字母的文本。 LOWER 不改变文本中的非字母字符。

 

UPPER(text)

将文本转换为大写字母。

  • Text    必需。 要转换为大写字母的文本。 文本可以是引用或文本字符串。

 

PROPER(text)

将文本字符串的首字母以及文字中任何非字母字符之后的任何其他字母转换成大写。 将其余字母转换为小写。

  • 文本    必需。 用引号括起来的文本、返回文本值的公式,或者对包含要进行部分大写转换文本的单元格的引用。

 

BAHTTEXT(number)

将数字转换为泰语文本并添加后缀“泰铢”。

  • Number    必需。 要转换成文本的数字、对包含数字的单元格的引用或结果为数字的公式。

 

DOLLAR(number, [decimals])

使用货币格式将数字转换为文本,将小数四舍五入到指定的位数。 DOLLAR 使用 $#,##0.00_) ; ($#,##0.00) 数字格式,尽管应用的货币符号取决于你的本地语言设置。

  • Number    必需。 数字、对包含数字的单元格的引用或是计算结果为数字的公式。
  • Decimals    可选。 小数点右边的位数。 如果为负数,则数字将舍入到小数点左侧。 如果省略 decimals,则假设其值为 2。

备注:通常,应该使用"设置单元格格式"选项将货币格式应用于单元格。 这是因为 DOLLAR 函数返回以文本格式提供的数量。 存储为文本的数字是电子表格错误的常见原因,因为许多函数会忽略它们,例如 SUM、AVERAGE、MIN、MAX 等。

 

FIXED(number, [decimals], [no_commas])

将数字舍入到指定的小数位数,使用句点和逗号,以十进制数格式对该数进行格式设置,并以文本形式返回结果。

  • Number    必需。 要进行舍入并转换为文本的数字。
  • decimals    可选。 小数点右边的位数。
  • no_commas    可选。 一个逻辑值,如果为 TRUE,则会禁止 FIXED 在返回的文本中包含逗号。

备注:

  • 在 Microsoft Excel 中,Numbers 的最大有效位数不能超过 15 位,但 decimals 可达到 127。
  • 如果 decimals 为负数,则 number 从小数点往左按相应位数四舍五入。
  • 如果省略 decimals,则假设其值为 2
  • 如果 no_commas 为 FALSE 或被省略,则返回的文本中和往常一样包含逗号。
  • 直接使用 FIXED 函数设置数字的格式将其结果转换为文本,使用"单元格"命令设置 格式 的编号仍然是数字。

 

NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])

以与区域设置无关的方式将文本转换为数字

  • 文本    必需。 要转换为数字的文本。
  • Decimal_separator    可选。 用于分隔结果的整数和小数部分的字符。
  • Group_separator    可选。 用于分隔数字分组的字符,例如,数千个数百个和数百万个和数千个。

备注:

  • 如果未指定 Decimal_separator 和 Group_separator 参数,则使用当前区域设置中的分隔符。
  • 如果 Decimal_separator 或 Group_separator 参数中使用了多个字符,则只会使用第一个字符。
  • 如果空字符串 ("") 被指定为文本参数,则结果为 0。
  • 文本参数中的空格(即使位于参数中间)也将被忽略。 例如,“ 3 000 ”将返回 3000。
  • 如果文本参数中多次使用小数分隔符,则 NUMBERVALUE 返回错误值 #VALUE! 。
  • 如果在文本参数中数组分隔符出现在小数分隔符之前,则将忽略数组分隔符。
  • 如果在文本参数中数组分隔符出现在小数分隔符之后,则 NUMBERVALUE 返回错误值 #VALUE! 。
  • 如果有任何参数无效,则 NUMBERVALUE 返回错误值 #VALUE! 。
  • 如果文本参数以一个或多个百分号 (%) 结束,这些百分号将用于结果计算。 如果文本参数中使用了多个百分号,则如同百分号用于公式中一样,这些百分号将累加。 例如,=NUMBERVALUE("9%%") 与公式 =9%% 返回的结果相同,都是 0.0009。

 

PHONETIC(reference)

提取文本字符串中的拼音 (furigana) 字符。该函数只适用于日文版。

 

REPT(text, number_times)

将文本重复一定次数。 使用 REPT 来在单元格中填充文本字符串的大量实例。

  • Text    必需。 需要重复显示的文本。
  • Number_times    必需。 用于指定文本重复次数的正数。

备注:

  • 如果 number_times 为 0(零),则 REPT 返回 ""(空文本)。
  • 如果 number_times 不是整数,将被截尾取整。
  • REPT 函数结果的长度不能超过 32,767 个字符,否则 REPT 返回 #VALUE!。

 

REPLACE(old_text, start_num, num_chars, new_text)

REPLACEB(old_text, start_num, num_bytes, new_text)

根据指定的字符数,REPLACE 将部分文本字符串替换为不同的文本字符串。

  • old_text    必需。 要替换其部分字符的文本。
  • start_num    必需。 old_text 中要替换为 new_text 的字符位置。
  • num_chars    必需。 old_text 中希望 REPLACE 使用 new_text 来进行替换的字符数。
  • Num_bytes    必需。 old_text 中希望 REPLACEB 使用 new_text 来进行替换的字节数。
  • new_text    必需。 将替换 old_text 中字符的文本。

 

SUBSTITUTE(text, old_text, new_text, [instance_num])

在文本字符串中用 new_text 替换 old_text。 如果需要在某一文本字符串中替换指定的文本,请使用函数 SUBSTITUTE;如果需要在某一文本字符串中替换特定位置处的任意文本,请使用函数 REPLACE。

  • text    必需。 需要替换其中字符的文本,或对含有文本(需要替换其中字符)的单元格的引用。
  • old_text    必需。 需要替换的文本。
  • new_text    必需。 用于替换 old_text 的文本。
  • Instance_num    可选。 指定要用 new_text 替换 old_text 的事件。 如果指定了 instance_num,则只有满足要求的 old_text 被替换。 否则,文本中出现的所有 old_text 都会更改为 new_text。

 

T(value)

返回值引用的文本。

备注:

  • 如果值是文字或引用文字,则 T 返回值。 如果值未引用文字,则 T 返回 ""(空文本)。
  • 由于 Microsoft Excel 会根据需要自动转换值,因此通常无需在公式中使用 T 函数。 提供此函数是为了与其他电子表格程序兼容。

 

TEXT(value, format_text)

TEXT 函数可通过格式代码向数字应用格式,进而更改数字的显示方式。

(见文件)

<<TEXT function examples.xlsx>>

 

VALUE(text)

将表示数字的文本字符串转换为数字

  • 文本    必需。 用引号括起来的文本或包含要转换文本的单元格的引用。

备注:

  • 文本可以是 Microsoft Excel 识别的任何常量数字、日期或时间格式。 如果文本不是这些格式之一,则 VALUE 返回#VALUE! 错误值。
  • 由于 Excel 会根据需要自动将文本转换为数字,因此通常无需在公式中使用 VALUE 函数。 提供此函数是为了与其他电子表格程序兼容。

 

VALUETOTEXT (value, [format])

OFFICE 365专属。返回来自任何指定值的文本。 它传递不变的文本值,并将非文本值转换为文本。

参数

描述

value   

必需

要作为文本返回的值。

format   

可选

返回数据的格式,两个值之一:

  • 0   默认。 易于阅读的简明格式。 返回的文本将与应用了常规格式的单元格中呈现的文本相同。
  • 1   包含转义字符和行定界符的严格格式。 生成一条可在输入编辑栏时被解析的字符串。 将返回的字符串(布尔值、数字和错误除外)封装在引号中。

注意: 如果格式不是 0 或 1,则 VALUETOTEXT 返回#VALUE! 错误值。

 

ARRAYTOTEXT(array, [format])

OFFICE 365专属。返回任意指定区域内的文本值的数组。 它传递不变的文本值,并将非文本值转换为文本。

参数

描述

array   

必需

要返回为文本的数组。

格式   

可选

返回数据的格式,两个值之一:

  • 0   默认。 易于阅读的简明格式。 返回的文本将与应用了常规格式的单元格中呈现的文本相同。
  • 1   包含转义字符和行定界符的严格格式。 生成一条可在输入编辑栏时被解析的字符串。 将返回的字符串(布尔值、数字和错误除外)封装在引号中。

注意: 

  • “简明”格式将返回一个单元格内的值的列表,而“严格”格式返回与输入大小和形状相同的数组。
  • 如果格式不是 0 或 1 之外的任何内容,ARRAYTOTEXT 将返回 #VALUE! 错误值。

 

CONCAT(text1, [text2],…)

2019版本。CONCAT 函数合并了多个范围和/或字符串中的文本,但不提供分隔符或 IgnoreEmpty 参数。

参数

描述

text1

(所需的)

要联接的文本项。 字符串或字符串数组,如单元格区域。

[text2, ...]

(可选)

要联接的其他文本项。 文本项最多可以有 253 个文本参数。 每个参数可以是一个字符串或字符串数组,如单元格区域。

备注:

  • 若要在要合并的文本之间包括分隔符 (如间距或与号 (&) ) ,并删除不想显示在合并后的文本结果中的空参数,可以使用 TEXTJOIN函数。
  • 如果结果字符串超过 32767 个字符(单元格限制),则 CONCAT 返回 #VALUE! 错误。

 

CONCATENATE(text1, [text2], ...)

将两个或多个文本字符串联接为一个字符串。

参数名称

说明

text1   (必需)

要联接的第一个项目。 项目可以是文本值、数字或单元格引用。

Text2, ...   (可选)

要联接的其他文本项目。 最多可以有 255 个项目,总共最多支持 8,192 个字符。

 

常见问题

问题

说明

引号显示在结果字符串中

使用逗号分隔相邻的文本项目。 例如:Excel 将 =CONCATENATE("Hello ""World") 显示为 Hello"World 与一个额外的双引号,因为文本参数之间的逗号被忽略。

数字不需要有引号。

单词混杂在一起

如果单独的文本项目之间缺少指定的空格,则文本项目将组合在一起。 请添加额外的空格作为 CONCATENATE 公式的一部分。 有两种方法可执行此操作:

  • 添加双引号并在 " 和 " 之间添加一个空格。 例如:=CONCATENATE("Hello", " ", "World!").。
  • 在 Text 参数后添加一个空格。 例如:=CONCATENATE("Hello ", "World!")。 字符串 "Hello " 包含添加的额外空格。

出现错误 #NAME?, 而不是预期的结果。

#NAME? 通常意味着 Text 参数中缺少引号。

最佳做法

要执行的操作

说明

使用与号 & 字符而不是 CONCATENATE 函数。

与号 (&) 计算运算符允许你在不使用函数的情况下联接文本项目。

例如,=A1 & B1 返回的值与 A1、B1 (=CONCATENATE) 。 在许多情况下,使用与号比使用 CONCATENATE 创建字符串的速度更快,更简单。

使用 TEXT 函数组合字符串和设置字符串格式。

TEXT 函数会将数值转换为文本,并将数字与文本或符号进行组合。

例如,如果单元格 A1 包含数字 23.5,可以使用以下公式将数字格式为美元金额:

=TEXT(A1,"$0.00")

结果:$23.50

 

TEXTJOIN(分隔符, ignore_empty, text1, [text2], …)

2019版本。TEXTJOIN 函数将多个区域和/或字符串的文本组合起来,并包括你在要组合的各文本值之间指定的分隔符。 如果分隔符是空的文本字符串,则此函数将有效连接这些区域。

参数

说明

delimiter

(必需)

文本字符串,或者为空,或用双引号引起来的一个或多个字符,或对有效文本字符串的引用。 如果提供一个数字,则将被视为文本。

ignore_empty

(必需)

如果为 TRUE,则忽略空白单元格。

text1

(必需)

要联接的文本项。 文本字符串或字符串数组,如单元格区域中。

[text2, ...]

(可选)

要联接的其他文本项。 文本项最多可以包含 252 个文本参数 text1。 每个参数可以是一个文本字符串或字符串数组,如单元格区域。

备注:如果生成的字符串超出单元格限制中的 32767 (,则 TEXTJOIN) 返回#VALUE! 错误。

{{commentTotal}} 条评论

{{item.nickname}}
{{item.create_date}}
{{item.content}}
- 上拉或点击加载更多 -
- 加载中 -
- 没有更多了 -
- 本文链接 -