如何在Excel中删除特殊字符

在本文中，您将学习如何从文本字符串中删除特定字符并同时从多个单元格中删除不需要的字符。

从其他地方将数据导入 Excel 时，大量特殊字符可能会进入您的工作表。更令人沮丧的是，有些字符是不可见的，这会在文本字符串之前、之后或内部产生额外的空白。本教程为所有这些问题提供了解决方案，让您不必逐个单元格地检查数据并手动清除不需要的字符。

从 Excel 单元格中删除特殊字符

要从单元格中删除特定字符，请使用最简单形式的SUBSTITUTE函数将其替换为空字符串：

替代（单元格，字符，“”）

例如，要消除 A2 中的问号，B2 中的公式为：

=SUBSTITUTE(A2, "?", "")

要删除键盘上不存在的字符，您可以将其从原始单元格复制/粘贴到公式中。

例如，以下是摆脱倒置问号的方法：

=SUBSTITUTE(A2, "¿", "")

但是，如果不需要的字符不可见或无法正确复制，您如何将其放入公式中？简单地说，使用 CODE 函数找到它的代码。

在我们的例子中，不需要的字符（“¿”）在单元格 A2 中最后出现，因此我们使用 CODE 和RIGHT函数的组合来检索其唯一的代码值，即 191：

=CODE(RIGHT(A2))

获得角色代码后，将相应的 CHAR 函数提供给上面的通用公式。对于我们的数据集，公式如下：

=SUBSTITUTE(A2, CHAR(191),"")

笔记。SUBSTITUTE 函数区分大小写，这意味着它将小写和大写字母视为不同的字符。如果您不需要的字符是字母，请记住这一点。

从字符串中删除多个字符

在单元格中有两个或多个不需要的字符的情况下，您可以将多个 SUBSTITUTE 函数嵌套到另一个中，以一次性消除它们：

替代（替代（替代（细胞，char1，“”），char2，“”），char3，“”）

例如，要从 A2 中的文本字符串中消除正常的感叹号和问号以及倒置的感叹号，请使用以下公式：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "!", ""), "¡", ""), "?", ""), "¿", "")

同样可以在 CHAR 函数的帮助下完成，其中 161 是“¡”的字符代码，191 是“¿”的字符代码：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3, "!", ""), "?", ""), CHAR(161), ""), CHAR(191), "")

嵌套的 SUBSTITUTE 函数适用于合理数量的字符，但如果要删除数十个字符，公式就会变得太长且难以管理。下一个示例演示了一个更紧凑、更优雅的解决方案。

一次删除所有不需要的字符

该解决方案仅适用于 Microsoft 365 的 Excel

您可能知道，Excel 365 有一个特殊功能，可让您创建自己的函数，包括那些递归计算的函数。这个新函数名为LAMBDA，您可以在上面链接的教程中找到有关它的完整详细信息。下面，我将通过几个实际示例来说明这个概念。

用于删除不需要的字符的自定义 LAMBDA 函数如下：

=LAMBDA(string, chars, IF(chars<>"", RemoveChars(SUBSTITUTE(string, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), string))

为了能够在您的工作表中使用此功能，您需要先命名它。为此，请按Ctrl + 3打开Name Manager，然后以这种方式定义一个新名称：

在名称框中，输入函数的名称：RemoveChars。
将范围设置为Workbook。
在引用框中，粘贴上述公式。
或者，在注释框中输入参数的描述。当您在单元格中键入公式时，将显示参数。
单击确定以保存您的新函数。

有关详细说明，请参阅如何命名自定义 LAMBDA 函数。

一旦函数获得名称，您就可以像任何本机公式一样引用它。

从用户的角度来看，我们自定义函数的语法很简单：

RemoveChars（字符串，字符）

在哪里：

字符串– 是原始字符串，或对包含字符串的单元格/范围的引用。
Chars – 要删除的字符。可以由文本字符串或单元格引用表示。

为方便起见，我们在某些单元格中输入不需要的字符，例如 D2。要从 A2 中删除这些字符，公式为：

=RemoveChars(A2, $D$2)

要使公式正常工作，请注意以下事项：

在 D2 中，列出的字符不包含空格，除非您也希望消除空格。
包含特殊字符的单元格的地址用 $ 符号 ($D$2) 锁定，以防止在将公式复制到下面的单元格时更改引用。

然后，我们只需将公式向下拖动，并从单元格 A2 到 A6 中删除 D2 中列出的所有字符：

要使用单个公式清除多个单元格，请为第一个参数提供范围 A2:A6：

=RemoveChars(A2:A6, D2)

由于公式仅在最顶部的单元格中输入，因此您不必担心锁定单元格坐标 – 在这种情况下，相对引用 (D2) 可以正常工作。由于支持动态数组，公式会自动溢出到所有引用的单元格中：

删除预定义的字符集

要从多个单元格中删除一组预定义的字符，您可以创建另一个调用主RemoveChars函数的 LAMBDA，并在第二个参数中指定不需要的字符。例如：

为了删除特殊字符，我们创建了一个名为RemoveSpecialChars的自定义函数：

=LAMBDA(string, RemoveChars(string, "?¿!¡*%#@^"))

为了从文本字符串中删除数字，我们又创建了一个名为RemoveNumbers的函数：

=LAMBDA(string, RemoveChars(string, "0123456789"))

上述两个函数都非常易于使用，因为它们只需要一个参数 – 原始字符串。

为了消除A2 中的特殊字符，公式为：

=RemoveSpecialChars(A2)

仅删除数字字符：

=RemoveNumbers(A2)

这个函数是如何工作的：

从本质上讲，RemoveChars函数循环遍历字符列表并一次删除一个字符。在每次递归调用之前，IF 函数都会检查剩余的字符。如果chars字符串不为空 (chars<>””)，则函数调用自身。处理完最后一个字符后，公式将字符串返回其当前形式并退出。

有关详细的公式分解，请参阅递归 LAMBDA 删除不需要的字符。

使用 VBA 删除特殊字符

这些函数适用于所有版本的 Excel

如果 LAMBDA 函数在您的 Excel 中不可用，则没有什么可以阻止您使用 VBA 创建类似的函数。用户定义函数 (UDF) 可以用两种方式编写。

递归删除特殊字符的自定义函数：

此代码模拟上述 LAMBDA 函数的逻辑。

函数 RemoveUnwantedChars(str As String, chars As String)
	如果 ("" <> 字符) 那么
		str = 替换(str, Left(chars, 1), "")
		字符 = 右（字符，Len（字符） - 1）
		RemoveUnwantedChars = RemoveUnwantedChars(str, chars)
	别的
		RemoveUnwantedChars = str
	万一
结束功能

用于删除非递归特殊字符的自定义函数：

在这里，我们从 1 到 Len(chars) 循环遍历不需要的字符，并将在原始字符串中找到的字符替换为空。MID 函数一一提取不需要的字符并将它们传递给 Replace 函数。

函数 RemoveUnwantedChars(str As String, chars As String)
	对于索引 = 1 到 Len(chars)
		str = 替换(str, Mid(chars, index, 1), "")
	下一个
	RemoveUnwantedChars = str
结束功能

如如何在 Excel 中插入 VBA 代码中所述，在您的工作簿中插入上述代码之一，您的自定义函数即可使用。

为了不将我们新的用户定义函数与 Lambda 定义的函数混淆，我们将其命名为不同的：

RemoveUnwantedChars（字符串，字符）

假设原始字符串在 A2 中，不受欢迎的字符在 D2 中，我们可以使用以下公式删除它们：

= RemoveUnwantedChars(A2, $D$2)

带有硬编码字符的自定义函数

如果您不想为每个公式提供特殊字符而烦恼，您可以直接在代码中指定它们：

函数 RemoveSpecialChars(str As String) As String
	将字符暗淡为字符串
	暗淡指数只要

	chars = "?¿!¡*%#$(){}[]^&/~+-"
	对于索引 = 1 到 Len(chars)
		str = 替换(str, Mid(chars, index, 1), "")
	下一个
	RemoveSpecialChars = str
结束功能

请记住，以上代码仅用于演示目的。为了实际使用，请务必在以下行中包含所有要删除的字符：

chars = "?¿!¡*%#$(){}[]^&/~+-"

这个自定义函数名为RemoveSpecialChars，它只需要一个参数 – 原始字符串：

删除特殊字符（字符串）

为了从我们的数据集中去除特殊字符，公式是：

=RemoveSpecialChars(A2)