Excel 子字符串技巧：提取与格式化文本

使用 Excel 子字符串函数按位置提取文本

我们经常希望根据位置从文本字符串中提取特定部分，例如某个单词或一段字符。Excel 提供了多种内置函数来帮助您提取文本的特定片段。最常用的函数包括 LEFT()、RIGHT()、 MID()、TEXTBEFORE() 和 TEXTAFTER()。下面逐一介绍。 

使用 LEFT() 子字符串函数

您可以使用 LEFT() 函数从文本字符串的开头提取字符。语法如下：

=LEFT(text, [num_chars])技术支持

其中： 

• text 指原始文本字符串所在的单元格地址。

• num_chars 指要提取的字符数。默认值为 1。

来看一个示例。这里我有一些产品编码，想从中提取前三个字符。 

=LEFT(A2,3)技术支持

使用 LEFT() 函数提取前三个字符。图片来源：作者。

使用 RIGHT() 子字符串函数

Excel 中的 RIGHT() 函数从文本字符串的末尾提取字符。语法如下：

=Right(text, [num_chars])技术支持

其中： 

• text 指原始文本字符串所在的单元格地址。

• num_chars 指要提取的字符数。默认值为 1。

再看一个示例。这里我用 RIGHT() 函数提取最后三个字符。

=RIGHT(A2, 3)技术支持

使用 RIGHT() 函数提取最后三个字符。图片来源：作者。

使用 MID() 子字符串函数

MID() 函数从文本字符串的中间提取字符。语法如下：

=MID(text, start_num, num_chars)技术支持

其中：

• text 指原始文本字符串所在的单元格地址。

• num_chars 指要提取的字符数。默认值为 1。

• start_num 指文本字符串中的起始位置。

在这个示例中，我使用 MID() 公式从第 4 个字符开始，提取中间的值。

=MID(A2,4,3)技术支持

使用 MID() 函数提取中间三个字符。图片来源：作者。 

这样就完成了。=MID(A2,4,3) 会从单元格 A2 的文本的第 4 个字符开始提取 3 个字符。

使用 FIND() 子字符串函数

Excel 中的 FIND() 函数用于在文本字符串中定位子字符串，并返回该子字符串首字符在字符串中的位置。语法如下：

FIND(find_text, within_text, [start_num]) 技术支持

其中：

• find_text 指要搜索的文本。

• within_text 是包含待查找文本的文本。

• start_num 指定开始搜索的位置，默认值为 1。

来看一个简单数据集，展示 FIND() 的工作方式。在此示例中，Text 列中有名称 DataCamp，我想定位子字符串 Camp 的位置。这会返回 5，因为 Camp 从该文本的第 5 个字符开始。

=FIND("Camp", A2)技术支持

使用 FIND() 函数获取文本位置。图片来源：作者。

使用 SUBSTITUTE() 子字符串函数

SUBSTITUTE() 函数将特定子字符串替换为另一个。语法如下：

 SUBSTITUTE(text, old_text, new_text, [instance_num])技术支持

其中：

• text 包含您想要更改的文本。

• old_text 指想要替换掉的文本。

• new_text 是用来替换旧文本的新文本。

• instance_num 指定要替换第几次出现的文本；若未指定，则替换所有出现。

为便于理解，我们看一个包含特定分隔符的文本字符串的简单数据集。Text 列列出了以分号为分隔符的姓名与年龄。为清洗这些数据，我输入以下公式：

=SUBSTITUTE(A2, ";" , ",")技术支持

其中：

• A2 指包含原始文本的单元格。

• ";" 指定我们要替换的旧文本。

• "," 是替换后的新文本。

使用 SUBSTITUTE 函数替换旧文本。图片来源：作者。

就是这样。在 B2 单元格应用该公式后，Excel 会将分号替换为逗号。

使用分隔符提取文本的 Excel 子字符串函数

分隔符是用来分隔两个文本字符串的特定字符，如分号或逗号。在 Excel 中，您可以使用 TEXTBEFORE() 与 TEXTAFTER() 函数在存在分隔符的情况下提取文本。  

使用 TEXTBEFORE() 子字符串函数 

顾名思义，TEXTBEFORE() 函数会提取位于指定字符或子字符串之前的文本。其语法如下：

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]技术支持

其中： 

• text 指原始文本。

• delimiter 通常为逗号或分号。

• instance_num 为分隔符的目标出现次数。默认值为 1。

• match_mode 指定搜索分隔符是否区分大小写（TRUE，默认）或不区分（FALSE）。

• match_end 确定是否将文本字符串的末尾视为分隔符。若为 TRUE，当未找到分隔符时函数将返回原始文本。

• if_not_found 指定在未找到分隔符时返回的自定义值。

现在用一个示例来看它的实际效果。我有一列 Text，包含员工详情，包括姓名、部门与分部地点。现在我只想提取姓名与部门。

=TEXTBEFORE(A2, ",", 2, 1, 1)技术支持

使用 TEXTBEFORE() 函数提取数据。图片来源：作者 

其工作原理如下： 

• A2 包含我要提取的文本。 

• "," 是原始数据中用于分隔文本的分隔符。 

• 2 表示函数将使用分隔符的第二次出现。

• 1 忽略大小写差异（如有）。

• 末尾的 1 表示若不存在分隔符则返回原始文本。

使用 TEXTAFTER() 子字符串函数

TEXTAFTER() 与 TEXTBEFORE() 类似——唯一不同在于结果方向。TEXTAFTER() 会提取出现在分隔符之后的文本。其语法如下：

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]技术支持

其中： 

• text 指原始文本。

• delimiter 为逗号或分号。 

• instance_num 为分隔符的目标出现次数。默认值为 1。

• match_mode 指定搜索分隔符是否区分大小写（TRUE，默认）或不区分（FALSE）。

• match_end 确定是否将文本末尾视为分隔符。若为 TRUE，当未找到分隔符时函数将返回原始文本。

• if_not_found 指定在未找到分隔符时返回的自定义值。

这里，我有一列 Text，包含员工详情：姓名、部门与分部地点。我希望从中提取员工的所在地与 ID。 

=TEXTAFTER(A2,",",2,1,1)技术支持

使用 TEXTAFTER() 函数提取数据。图片来源：作者 

其工作原理如下： 

• A2 包含我要提取的文本。 

• ","  是原始数据中用于分隔文本的分隔符。 

• 2 表示函数将使用分隔符的第二次出现。 

• 1 忽略大小写差异（如有）。

• 1 （在末尾） 表示若不存在分隔符则返回原始文本。

Excel 子字符串的特定用例

现在您已掌握子字符串的基础知识，是时候学习更进阶的方法了。这些方法可以实现更复杂的文本处理，让您的数据处理工作更加高效。 

处理不同长度的名字

我有一个包含全名的数据集，但我只想提取名（first name）。

包含全名的表格。图片来源：作者。

您可能会想这很简单——用 LEFT() 函数获取名即可。所以我尝试使用以下公式：

=LEFT(A2,4)技术支持

使用 LEFT() 函数提取名。图片来源：作者。

但我得到的并不是“名”，而是前四个字符——因为我把 num_char 设为了 4（Jane 的名字正好 4 个字符）。当我向下复制公式时，其他名字的结果并不正确。

我需要一个更灵活的方案。要不受长度影响地提取名，可以将 LEFT() 与 FIND() 组合使用。我用 FIND() 找到全名中第一个空格的位置（分隔名与姓），然后用 LEFT() 提取直到该位置之前的字符。 

=LEFT(A2,FIND(" ",A2)-1)技术支持

组合使用 FIND() 与 LEFT() 提取名。图片来源：作者。

下面拆解公式以便理解其工作方式：

• A2 单元格包含全名。

• FIND(" ", A2, 1) 从第一个字符开始，找到 A2 文本字符串中第一个空格的位置。

• -1 用于去掉空格。

• LEFT(A2, ...) 从文本左侧提取指定数量的字符。

从电子邮箱地址中提取域名

再来看一个用例，检验 LEFT() 与 FIND() 是否能应对不同场景。我有一组电子邮箱 地址，需要从中提取域名。

包含邮箱地址的数据。图片来源：作者。

为此，我创建了一列 Email Domain（邮箱域） ，并在另一个单元格输入以下公式，然后复制到另外三个单元格：

=RIGHT(A2, LEN(A2) - FIND("@",A2))技术支持

使用 RIGHT()、LEN() 与 FIND() 提取域名。图片来源：作者。

好了——我已经得到所有域名。下面解释 Excel 如何得到结果：

• FIND("@", A2) 定位邮箱地址中@ 符号的位置。

• LEN(A2) 计算邮箱地址的总长度。

• LEN(A2) - FIND("@", A2) 计算在@ 符号之后的字符数。

• 最后，RIGHT(A2, LEN(A2) - FIND("@", A2)) 从字符串右端提取相应数量的字符。

组合 LEFT()、MID() 与 RIGHT() 提取产品编码

前文分别介绍了 LEFT()、MID() 与 RIGHT() 的用法。但将这些函数组合起来，能实现更多操作。它们可以根据特定模式提取文本字符串的不同部分。

这里我有一列产品编码 ，我想从中提取特定片段。 

• 第一段提取 1 个字符。
• 第二段提取全部 4 个字符。
• 最后一段提取最后 3 个字符。

产品编码列表。图片来源：作者。

为实现上述目标，我组合使用 LEFT()、RIGHT() 与 MID()，并输入以下公式：  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)技术支持

组合使用 MID()、LEFT() 与 RIGHT() 提取数据。图片来源：作者。

如您所见，这个组合公式从各个段中提取了所需字符。具体如下：

• LEFT(A2, 1) 提取第一个字符。

• MID(A2, FIND("-", A2) + 1, 4) 定位第一个连字符，向右移动一位，然后提取 4 个字符。

• "-" 添加一个连字符。

• RIGHT(A2, 3) 提取最后三个字符。

• & 用于连接各部分字符。 

将 SUBSTITUTE() 与 MID() 结合进行动态提取 

接下来看看如何将 MID() 与 SUBSTITUTE() 结合，既能提取文本字符串的特定部分，又能在该部分内替换字符或子字符串。

例如，我有客户 Order Details（订单详情），想将订单 ID 替换为Order confirmed。

包含订单详情的表格。图片来源：作者。

我使用以下公式：

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")技术支持

使用 SUBSTITUTE() 与 MID() 函数替换文本。图片来源：作者。

就这样！您可以看到该公式按我的需求提取并替换了文本字符串的特定部分。

• MID(A2, 1, 19) 从 A2 单元格的文本中提取前 19 个字符。

• SUBSTITUTE(text, old_text, new_text) 将出现的 old_text 替换为 new_text。

再看一个示例：这里我有一列 Product Detail（产品详情），我想从方括号中提取产品尺寸。

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")技术支持

从方括号中提取产品尺寸。图片来源：作者。

完成——它已经提取出所有细节。

• FIND("[", A2) 找到左方括号的起始位置。

• FIND("]", A2) 找到右方括号的结束位置。

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) 提取两个括号之间的文本。

• SUBSTITUTE(...,"[", "") 去掉提取文本中的左方括号。

清理数据记录

有时我们的数据并不规范，但您完全没必要直接使用这些数据。这里我有一列电话号码，格式各异，但我想将其规范为一致格式，清理条目中的不一致之处。

未格式化的电话号码。图片来源：作者。

为实现标准化，我创建一列名为 Clean data（清洗数据）。我在第二个单元格输入以下公式，并复制到所有需要显示结果的单元格：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")技术支持

其中： 

• SUBSTITUTE(A2, "-", "") 去除连字符。

• SUBSTITUTE(..., "(", "") 去除左括号。

• SUBSTITUTE(..., ")", "") 去除右括号。

• SUBSTITUTE(..., ".", "") 去除句点。

这样就把多种格式转换为连续的数字串，如下图所示。现在数据已清理、格式规整，可以直接使用。当然，您也可以继续优化。

使用 SUBSTITUTE() 函数清理数据。图片来源：作者。

为报告格式化文本

如果您想在清洗数据后更进一步，对其进行恰当的格式化，也可以使用不同的子字符串函数。

继续以上电话号码示例。现在我想将这些数字格式化 ，让它们看起来更像电话号码而非纯数字串。 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")技术支持

使用多个子字符串函数将数字标准化为统一格式。图片来源：作者。

完成。我已经得到想要的格式。其工作原理如下：

• LEFT(B2, 3) 提取前三位数字。

• MID(B2, 4, 3) 从第 4 位开始提取接下来的 3 位数字。

• RIGHT(B2, 4) 提取最后四位数字。

• TEXT(..., "000") 和 TEXT(..., "0000") 对各段进行格式化，必要时补零以保证位数。

• & "-" & 用连字符连接各段。

结语

您已经学会使用 Excel 的子字符串函数，如 LEFT()、RIGHT()、MID()、TEXTBEFORE() 与 TEXTAFTER() 来处理文本数据。无论是提取字符串的特定部分，还是清理杂乱数据，这些函数都能让您的工作更轻松高效。

但 Excel 的学习总有更多。若您刚入门，Introduction to Excel 课程是很好的下一步。如果您想深入数据分析，可以学习 Data Analysis in Excel 课程，并结合我们的 Data Manipulation in Excel Cheat Sheet。这些资源将帮助您夯实基础，进一步提升技能。