Excel17个文本函数的用法,Excel 17个文本函数 列表

题图来自Unsplash，基于CC0协议

导读

Excel是处理数据的强大工具，而文本函数则是其处理字符串信息的核心。掌握这17个常用文本函数，能让你在数据清洗、报表制作、甚至公式推导中事半功倍。它们专注于文本（字符串）的提取、修改、组合以及格式化。

首先，让我们来看看这17个文本函数的列表：CONCAT, TEXTJOIN, LEFT, RIGHT, MID, LEN, TRIM, CONCATENATE, REPEAT, REPT, SUBSTITUTE, REPLACE, SEARCH, FIND, UPPER, LOWER, PROPER。

今天我们将主要聚焦在这几个关键方向：

文本提取三剑客：LEFT, RIGHT, MID

这三个函数的核心作用是从文本字符串中精确地“挖”出你需要的部分。

• LEFT(text, [num_chars])：函数从指定文本字符串的开始处（左边）提取指定数量的字符。如果你知道电话号码格式为“区号-xxxxxxxxx”（例如 "(861)-555-1234"），想直接获取国家代码“+861”或更前面的区号，这函数就派上用场了。你需要知道的是具体要从左边数多少位。如果省略num_chars，将提取整个字符串。
• RIGHT(text, [num_chars])：与LEFT方向相反，RIGHT是从文本字符串的末尾（右边）开始提取指定数量的字符。比如，要从一个固定格式的编码（如 “100012****”）中提取最后的几个星号后面的核心数字部分，RIGHT就非常方便。
• MID(text, start_num, [num_chars])：MID函数则更灵活，它允许你指定一个起始位置（start_num）和从该位置开始要提取的字符长度（num_chars）。这是当你需要从字符串中间某个特定点开始截取信息时的首选，例如在一个名单字符串中提取某个位置的信息。

寻找特定字符和函数应用：SEARCH, FIND, SUBSTITUTE, REPLACE, 处理文本连续空格和长度：TRIM, LEN 文本组合：CONCAT, TEXTJOIN, CONCATENATE, REPEAT 文本格式化与大小写转换：TEXT, UPPER, LOWER, PROPER 重复指定文本：REPT

Excel TEXT函数与格式代码

处理日期、数字等原始数据时，我们常常需要将它们转换成易于阅读或符合要求格式的文本，这就是TEXT函数的专长。它使用特殊的格式代码来定义输出文本的格式。

例如，将单元格A1中的日期数据转换为中文的“2023年11月21日星期二”格式，公式写作 =TEXT(A1, "yyyy年m月d日星期" & WEEKDAY(A1,2)) （假设WEEKDAY能用于构造），具体的日期格式代码非常多：

• m/d 或 m/d/yyyy：月份/日 或 月份/日/年，格式可变。
• "mm/dd/yyyy"：强制指定是两位数。
• LETTERS：如 "January", "一二三四五"，只需在代码加引号。
• h: 小时，不带零。
• hh: 小时，带零。
• m: 分钟。
• ss: 秒数。
• AM/PM: 上午/下午。
• yy 或 yyyy: 年份，两位或四位。
• 例如 =TEXT(TODAY(), "m月d日") 会显示 “11月21日”。

文本替换与搜索：SUBSTITUTE, REPLACE, SEARCH, FIND

有时你需要根据特定条件或关键字替换文本内容。

• SUBSTITUTE函数：用一个新的文本字符串替换原始文本字符串中所有指定的旧文本字符串，例如，你可以用“Fly”批量替换“Flying”。
• 和它常常配对使用的是SEARCH和FIND函数（以及LEFT, RIGHT, MID），前者在不区分大小写的情况下搜索，后者区分大小写。
  • SEARCH("apple", A1, [start_num])：在文本字符串A1中查找子字符串"apple"的位置，区分大小写吗？不，SEARCH不区分大小写。它会返回第一个找到的位置的数字。如果找不到，返回错误值。
  • FIND("apple", A1, [start_num])：与SEARCH功能相同，但是区分大小写。
• REPLACE函数：用于替换文本字符串中特定位置且长度为指定字符的字符串。你必须明确要替换的起始位置和字符数，这需要结合SEARCH或FIND来决定替换范围和内容。例如，将一个固定格式的日期串中的某一部分替换掉。SUBSTITUTE则是在字符串中的任何出现位置替换，一次性替换所有。
• 重点区分SUBSTITUTE和REPLACE：
  • 用途场景：SUBSTITUTE主要用于根据固定的旧文本查找并替换为新文本（通常文本是针眼不变的）；REPLACE用于基于位置（如，某个字符串中固定位置的X个字符）进行替换，通常需要其他函数辅助找到替换位置。
  • 灵活性：SUBSTITUTE匹配的是整个文本串，而REPLACE操作的是特定范围。
  • SEARCH/FIND：这只是用于查找文本存在位置的辅助函数，判断某个文本出现在哪里，以便MID``LEFT等函数定位，或是REPLACE的输入。

仅举一例：SUBSTITUTE可以直接根据字符串进行批量替换，无需考虑位置。REPLACE需要你事先知道要替换的内容范围（位置和长度），或者通过其他函数计算出来。

最后，谈谈Excel的文本组合：CONCAT、CONCATENATE、TEXTJOIN

在处理数据时，我们常常需要将多个单元格或变量的内容连接起来生成一个完整的字符串（文本），这就是文本组合函数的作用。

除了基础的CONCATENATE函数（在较老版本Excel中）以及直接使用&运算符 A1&A2，Excel提供了更强大的TEXTJOIN函数。

TEXTJOIN的一大优势是，它可以携带一个是否忽略空值的参数ignore_empty。这意味着在连接多个可能为空字符串的单元格时，你可以告诉函数自动跳过空值，避免连出来奇怪的“ ”，还能自定义连接符，甚至控制每个文本部分之间的空格添加，灵活性是&运算符和基础CONCATENATE所不具备的。

还有一个显著的函数是CONCAT，它在Excel 2016及以上版本（含Office 365）中作为TEXTJOIN的补充，可以直接用在范围上，比如=CONCAT(A1:C10) 将合并A1:C10区域内所有单元格的内容，而且它在很多默认情况下也优于&，但在处理空单元格时的精确控制不如TEXTJOIN。

总结

这17个函数是Excel中处理文字数据的“瑞士军刀”，掌握它们对于提高工作效率，特别是在数据整理、清洗和分析阶段至关重要。从基本的提取字符、组合文本，到复杂的替换、搜索，再到专业的日期格式化与大小写调整，熟练运用这些函数能让你的Excel技能更上一层楼。建议通过不同示例反复练习，才能牢固掌握这些强大的文本处理能力。