Excel SUBSTITUTE 函数

在 Excel 中用另一个文本替换文本是一个非常普遍的操作，我们每个人都有不同的方法来完成。但你知道 Excel 提供了名为 SUBSTITUTE 的内置函数 来替换一个或多个文本字符串吗？在本教程中，我们将深入探讨 SUBSTITUTE 函数。

什么是 SUBSTITUE 函数？

Excel 的 Substitute 函数可以将字符串中的一部分文本替换为其他文本。SUBSTITUTE 函数用于通过替换导入数据中的一个或多个字符来清理数据。例如，如果我们从 PDF 文件导入了格式为 458-299-605 的数据，我们可以应用 SUBSTITUTE 函数将其转换为 458299605。

Excel 的 SUBSTITUTE 函数用其他文本字符串替换或替换文本字符串。您可以使用 SUBSTITUTE 函数根据匹配项替换文本（请记住，它不按位置工作）。您还可以用空数据替换一个或多个文本字符串；您需要在参数中传递一个空字符串 ("")。SUBSTITUTE 是 Excel 文本函数类别中的一个内置函数。

SUBSTITUTE 函数区分大小写。您可以在 Excel 中将其用作工作表函数。SUBSTITUTE 函数可以作为工作表函数插入到单元格中的另一个公式中。此函数允许您轻松地替换字符串中的文本，并使您能够决定要替换字符串的哪一部分。此函数的可选替代方案是使用 Excel 的查找和替换功能。

注意：单个 SUBSTITUTE 函数一次只能替换一个文本值。但是，您可以将 SUBSTITUTE 函数嵌套在自身内部以替换多个字符串。

语法

参数

• 文本 (必需)：此参数指定您要在其中替换文本的文本。它指的是字符串的引用单元格。
• 旧文本 (必需)：此参数指定您要替换的旧文本。
• 新文本 (必需)：此参数指定您要用哪个新文本替换原始文本。
• 实例编号 (可选)：您要用新文本替换旧文本的出现次数。当您跳过实例参数时，旧文本参数的所有出现都将被新文本参数替换。

SUBSTITUTE 函数的要点

• SUBSTITUTE 函数在指定的字符串中搜索旧文本参数并用新文本替换它。
• 实例限制 SUBSTITUTE 替换旧文本的特定实例。
• 当您跳过实例参数时，旧文本参数的所有出现都将被新文本参数替换。
• SUBSTITUTE 是一个区分大小写的函数，不支持通配符。

示例 1：使用 SUBSTITUTE 函数对下面的某些字符串进行更改，以获得“结果”列中提到的结果。

要替换您的数据，请遵循以下步骤

步骤 1：添加一个名为“输出”的辅助列

将鼠标光标放在“结果”旁边的单元格上，并将新列命名为“输出”。

它将看起来与下图相似

我们将在该列中键入 SUBSTITUTE 公式，并为不同的文本值放置 SUBSTITUTE 数据。

注意：格式化辅助列并使其与匹配列匹配，以使您的 Excel 表格更具吸引力。

步骤 2：键入 SUBSTITUTE 公式

将光标放在第二行，然后开始键入函数：= SUBSTITUTE(

它将看起来与下图相似

步骤 3：插入参数

• 文本：在这里我们将指定我们想要更改的文本。它是字符串的引用单元格，例如 B4 或 HUT，因此我们的公式变为：=SUBSTITUTE(B4,
  
• 旧文本：在此参数中，我们将指定我们要更改的字符串（我们现有的文本字符串）。在这里，在我们的例子中，我们想更改字符串 b，所以用双引号键入它，即“b”，因此我们的公式变为：=SUBSTITUTE(B4, "b",
  
• 新文本：在这里，我们将指定我们想要对旧文本进行的更改。它将以双引号输入。与此处不同，我们希望用“t”替换旧文本，因此我们的公式变为=SUBSTITUTE(B4, "b","t"
• 实例编号：此参数指示我们正在针对哪个特定实例进行替换。由于这里只有一个“b”的出现，我们将跳过这个可选参数。

我们的最终公式将类似于下图：

步骤 4：SUBSTITUTE 将返回结果

SUBSTITUTE 将用“t”替换字符“b”，并返回“Bot”作为输出。

它将看起来与下图相似

步骤 5：对其他行重复该公式

• 在 E5 单元格中，键入公式：= SUBSTITUTE (B5,"B","T",1)。由于 Bumble 有两个 b，我们在实例 1 中指定了它，因此它将用字母 T 替换第一个 b。
  
• 在 E6 单元格中，键入公式：=SUBSTITUTE (B6,"easy to use"," convenient")。此函数将把字符串“easy to use”替换为 convenient，如下图所示
  
• 在 E7 单元格中，键入公式：=SUBSTITUTE (B7,"the","an",2)。因为文本“the Excel is the easy application”中有两个 the。因此，我们在实例 2 中指定了它，它将用字母“an”替换第二个“the”。
  
• 在 E8 单元格中，键入公式：=SUBSTITUTE (B8,"The"," ")。SUBSTITUTE 函数区分大小写。因此，它会将“The”和“the”视为不同的字符串。此函数将用空字符串替换字符串“The”。
  

有关所有输出，请参阅下图

好了，您已经成功学会了如何在 Excel 中使用 SUBSTITUTE 函数。让我们来探索一些复杂的例子。

示例 2：使用嵌套 SUBSTITUTE 函数演示，如下表所示

SUBSTITUTE 函数不能在一个函数中替换多个字符串。由于在上表中，我们需要替换所有三个字符串，因此我们需要在同一个单元格中使用三个单独的 SUBSTITUTE 函数来完成替换。为了实现这一点，最好的方法是将一个 SUBSTITUTE 函数嵌套在另一个函数中。

要理解嵌套的 SUBSTITUTE 函数，请遵循以下步骤

步骤 1：添加一个名为“SUBSTITUTE Output”的辅助列

将鼠标光标放在“原始文本”旁边的单元格上，并将新列命名为“SUBSTITUTE Output”。

它将看起来与下图相似

我们将在该列中键入 SUBSTITUTE 公式，并为不同的文本值放置 SUBSTITUTE 数据。

步骤 2：键入 SUBSTITUTE 公式

• 将光标放在第二行，然后键入：= SUBSTITUTE( 开始嵌套公式
• 由于我们有三个要替换的字符串，因此我们将嵌套三个 SUBSTITUTE 函数，一个嵌套在另一个里面。公式将是：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  

步骤 3：插入嵌套 SUBSTITUTE 的参数

• 首先，我们将输入第三个 SUBSTITUTE 函数的参数。我们将字符串“Boy”替换为字符串“M”。公式将变为：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C4,"Boy","M")
  
• 接下来，我们将输入第二个 SUBSTITUTE 函数的参数。我们将用新字符串“Rahul Panda”替换旧字符串“Rahul”。在第二个嵌套函数中，您无需再次输入文本参数；我们只需要指定旧文本和新文本参数。公式将变为：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C4,"Boy","M"),"Rahul","Rahul Panda"),
  
• 对于第三个嵌套函数，添加参数。我们将用新字符串“EMP001”替换旧字符串“E001”。在第三个嵌套函数中，您无需再次输入文本参数；我们只需要指定旧文本和新文本参数。公式将变为

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C4,"Boy","M"),"Rahul","Rahul Panda"),"E001","EMP001")

我们的公式将类似于下图

步骤 4：SUBSTITUTE 将返回结果

嵌套的 SUBSTITUTE 函数将用新字符串替换所有旧字符串，并一次性返回数据。

输出将类似于下图

步骤 5：对以下各行重复上述公式

• 在 D5 单元格中，键入公式：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,"Girl", "F"),"Reema","Reema Panda"),"E002", "EMP002").
  它将用新文本替换所有旧文本，并为您提供结果。
  
• 在 D6 单元格中，键入公式：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C6,"Girl", "F"),"Sita", "Sita Garg"),"E003","EMP003")
  
• 在 D7 单元格中，键入公式：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C7,"Boy","M"),"Madhav","Madhav Jain"),"E004","EMP004").

它将用新文本替换所有旧文本，并为您提供结果。

嵌套的 SUBSTITUTE 函数将用新字符串替换所有旧字符串，并为所有行返回输出。

请参考下图

示例 3：使用 SUBSTITUTE 函数删除 Excel 工作表中的换行符。

要替换文本中的换行符，可以使用 SUBSTITUTE 和 CHAR 函数的组合。要删除单元格中的换行符，请按照以下步骤操作

步骤 1：添加一个名为“SUBSTITUTE Output”的辅助列

将鼠标光标放在“字符串”旁边的单元格上，并将新列命名为“输出”。

它将看起来与下图相似

在此列中，我们将键入 SUBSTITUTE 公式，并为不同的文本值放置 SUBSTITUTE 数据。

步骤 2：键入 SUBSTITUTE 公式

将光标放在第二行，然后开始键入函数 = SUBSTITUTE(

它将看起来与下图相似

步骤 3：插入参数

SUBSTITUTE 可以定位单元格中任何位置的匹配文本，并用您选择的文本替换它。SUBSTITUTE 最多可以接受四个参数，但我们将只使用前三个并跳过第四个参数。

• 在文本参数中，我们将指定要从中删除换行符的字符串。它是字符串的引用单元格，即 B2，因此我们的公式变为：=SUBSTITUTE(B4,
  
• 在第二个参数（旧文本）中，我们将指定换行符，因为这是我们要替换的内容。我们知道 Excel 中的 ASCII 字符集是 CHAR(10)，因此我们的公式变为：=SUBSTITUTE(B2, CHAR (10)
  
• 在第三个参数中，我们将指定我们要用哪个新文本替换旧文本，在我们的情况下是逗号 (,)，因此我们的公式变为：=SUBSTITUTE(B5,CHAR(10, ",")),", ")

请参考下图

步骤 4：SUBSTITUTE 将返回结果

SUBSTITUTE 函数将用逗号 (,) 替换 B4 单元格中的所有换行符或 Char (10) 字符，并将返回最终输出，即单元格 C4 中的“Red, Yellow, Orange”。

请参考下图

步骤 5：将公式拖到其他行以重复

• 将鼠标光标放在公式单元格上，并将光标指向单元格的右下角。令您惊讶的是，鼠标指针将变为“+”图标。
• 将 '+' 图标向下拖动到单元格。它会将 SUBSTITUTE 函数复制到所有单元格，并相应地更改单元格引用。