以删除Excel中的某些字符或文字的正则

您是否曾经想过，如果有人可以用正则表达式丰富其工具箱，那么Excel会有多强大？我们不仅想到了，而且努力了:)现在，您可以在自己的工作簿中添加这个出色的Regex功能，并立即擦除与图案相匹配的子字样！

上周，我们研究了如何使用正则表达式替换Excel中的字符串。为此，我们创建了一个自定义的正则替换功能。事实证明，该功能超出了其主要用途，不仅可以替换字符串，还可以删除它们。那怎么可能？就excel而言，删除值只不过是用一个空字符串代替它，这是我们的Regex功能非常擅长的！

• VBA REGEXP函数以删除子字符串
• Excel Regex删除示例
  • 删除所有匹配或特定匹配
  • 剥离某些字符
  • 删除特殊字符
  • 删除非数字字符
  • 脱掉空间
  • 在某些字符之后删除文字
  • 清除空间之前的一切
  • 消除角色之前的一切
  • 删除除了除外的所有内容
  • 删除HTML标签的正则表达式
• ablebits正则删除工具

VBA REGEXP函数以删除Excel中的子字符串

众所周知，默认情况下，Excel不支持正则表达式。要启用它们，您需要创建自己的用户定义功能。好消息是，这种功能已经编写，测试和准备使用。您要做的就是复制此代码，将其粘贴到VBA编辑器中，然后将文件保存为宏支持的工作簿（.xlsm）。

该函数具有以下语法：

regexpreplace（文本，模式，替换，[instance_num]，[match_case]）

需要前三个参数，最后两个是可选的。

在哪里：

• 文本- 要搜索的文本字符串。
• 模式- 要搜索的正则表达式。
• 替换- 要替换​​的文本。要删除与图案匹配的子字符串，请使用一个空字符串（“”）进行更换。
• instance_num （可选） - 要替换​​的实例。如果省略，所有发现的匹配都将更换（默认）。
• match_case （可选） - 一个布尔值，指示是匹配还是忽略文本案例。对于对案例敏感的匹配，请使用true（默认）;对于案例不敏感的 - 错误。

有关更多信息，请参阅Regexpreplace功能。

提示。在简单的情况下，您可以使用Excel公式从单元格中删除特定字符或单词。但是正则表达式为此提供了更多选择。

如何使用正则表达式删除字符串 - 示例

如上所述，要删除与图案匹配的文本的一部分，您将用空字符串替换它们。因此，通用公式采用这种形状：

regexpreplace（文本，模式，“”，[instance_num]，[match_case]）

以下示例显示了此基本概念的各种实现。

删除所有匹配或特定匹配

Regexpreplace函数旨在查找与给定的正则匹配的所有子字符串。要删除的事件由第4^个可选参数（名为instance_num）控制。

默认值为“所有匹配” - 当省略了instance_num参数时，所有发现的匹配将删除。要删除特定的匹配，请定义实例号。

在以下字符串中，假设您要删除第一阶号。所有此类数字从哈希符号（＃）开始，并完全包含5位数字。因此，我们可以使用以下等级来识别它们：

模式：＃\ d {5} \ b

单词边界\ b指定匹配的子字符串不能是诸如＃10000001之类的较大字符串的一部分。

要删除所有匹配项，未定义实例_num参数：

=RegExpReplace(A5, "#\d{5}\b", "")

要仅消除第一次出现，我们将instance_num参数设置为1：

=RegExpReplace(A5, "#\d{5}\b", "", 1)

删除某些字符的正则

要从字符串中剥离某些字符，只需写下所有不需要的字符，然后用垂直条将它们分开|在Regexes中充当或操作员。

例如，为了标准以各种格式编写的电话号码，首先我们要摆脱特定字符，例如括号，连字符，点和空格。

模式：\（| \）| - | \。| \ s

=RegExpReplace(A5, "\(|\)|-|\.|\s", "")

此操作的结果是一个10位数字，例如“ 1234567890”。

为了方便起见，您可以输入正则是一个单独的单元格，并使用绝对参考（例如$ a $ 2：）参考该单元格。

=RegExpReplace(A5, $A$2, "")

然后，您可以使用串联操作员（＆）以及右，中和左的文本功能标准化所需的格式。

例如，要在（123）456-7890格式中写下所有电话号码，公式为：

="("&LEFT(B5, 3)&") "&MID(B5, 4, 3)&"-"&RIGHT(B5, 4)

其中B5是Regexpreplace函数的输出。

使用正则删除特殊字符

在我们的一个教程中，我们研究了如何使用内置和自定义功能在Excel中删除不必要的字符。正则表达使事情变得容易得多！而不是列出要删除的所有字符，而只需指定要保留的字符:)

该模式基于否定的字符类- 将Caret放置在字符类中[^]，以匹配任何不括号中的单个字符。量词迫使其将连续的字符视为单个匹配，以便为匹配的子字符串而不是每个单独的字符进行替换。

根据您的需求，请选择以下言论之一。

要删除非α数字字符，即除字母和数字以外的所有字符：

模式：[^0-9A-ZA-Z]

清除除字母，数字和空间以外的所有字符：

模式：[^0-9A-ZA-Z]

要删除除字母，数字和下划线以外的所有字符，您可以使用代表任何不是字母数字字符或下划线的字符的\ w：

模式：\ w

如果您想保留其他一些字符，例如标点符号，请将它们放在括号内。

例如，要剥离字母，数字，周期，逗号或空间以外的任何字符，请使用以下正则言论：

模式：[^0-9a-Za-Z \。，]

这成功消除了所有特殊角色，但额外的空格仍然存在。

为了解决此问题，您可以将上述功能嵌套到另一个函数中，该功能将多个空格替换为单个空间字符。

=RegExpReplace(RegExpReplace(A5,$A$2,""), " ", " ")

或者只使用具有相同效果的本机内饰函数：

=TRIM(RegExpReplace(A5, $A$2, ""))

删除非数字字符的正则

要从字符串中删除所有非数字字符，您可以使用此长公式或下面列出的非常简单的Regexes之一。

匹配任何不是数字的角色：

模式：\ d

使用否定类剥离非数字字符：

模式：[^0-9]

模式：[^\ d]

提示。如果您的目标是删除文本并将剩余的数字溢出到单独的单元格中，或将它们全部放在一个用指定的定界符分离的单元中，则使用Regexpextract函数，如如何使用正则表达式从字符串中提取数字中所述。

正则删除所有事物

要在空间之后擦除所有内容，请使用空间（）或whitespace（\ s）字符查找第一个空间和。*以匹配其之后的任何字符。

如果您的单线字符串仅包含正常空间（在7位ASCII系统中值32），则您使用的以下式以下言论中的哪一个并不重要。在多行字符串的情况下，确实有所作为。

要在空间字符之后删除所有内容，请使用此正则态度：

图案： ” 。*”

=RegExpReplace(A5, " .*", "")

此公式将在每行第一个空间之后剥离任何东西。为了正确显示结果，请确保打开包裹文本。

要在空格之后剥离所有内容（包括空间，标签，马车返回和新线路），正则是：

模式：\ s。*

=RegExpReplace(A5, "\s.*", "")

因为\ s匹配了几种不同的空格类型，包括新行（\ n），因此该公式在单元格中第一个空间之后删除了所有内容，无论其中有多少行。

正则删除特定字符的文字

使用上一个示例中的方法，您可以在指定的任何字符之后根除文本。

分别处理每条线：

通用模式：char。*

在单线字符串中，这将在字符之后删除所有内容。在多行字符串中，每行将被单独处理，因为在VBA Regex风味中，一个（。）匹配除了新线之外的任何字符。

作为单个字符串处理所有行：

通用模式：char（。| \ n）*

要在给定字符之后删除任何内容，包括新行，\ n将添加到模式中。

例如，要在字符串中的第一个逗号之后删除文本，请尝试以下正则表达式：

图案： ，。*

模式：，（。| \ n）*

在下面的屏幕截图中，您可以检查结果的不同。

正则要在空间之前删除所有内容

使用长文本时，您有时可能需要通过删除所有单元格中的相同部分来使其更短。下面我们将讨论两个这样的情况。

在最后一个空间之前删除所有内容

与上一个示例一样，正则表达式取决于您对“空间”的理解。

为了将任何内容匹配到最后一个空间，此正则是这样的（添加了引号标记以在明显的星号之后添加一个空间）。

图案： ”。* ”

要匹配最后一个空格之前的任何内容（包括空间，标签，托架返回和新线路），请使用此正则表达式。

模式：。*\ s

在多行字符串上，差异尤其明显。

在第一个空间之前剥离一切

要将任何内容匹配到字符串中的第一个空间，您可以使用此正则表达式：

模式： ^ [ ^]*

从字符串的开头 ^，我们匹配零或多个非空间字符[ ^]*，后者紧随其后的一个或多个空格“”。添加最后一部分是为了防止结果中的潜在领先空间。

要在每行的第一个空间之前删除文本，该公式以默认的“所有匹配”模式写入（省略了）：

=RegExpReplace(A5, "^[^ ]* ", "")

要在第一行的第一个空间之前删除文本，然后将所有其他行留下完整，请将instance_num参数设置为1：

=RegExpReplace(A5, "^[^ ]* ", "", 1)

正则剥夺角色之前的一切

在特定字符之前，使用这样的正则以下的正则删除所有文本的最简单方法：

通用模式： ^[ ^char]*char

它翻译成一种人类语言，它说：“从锚定的字符串开始，匹配0或更多字符，除了char [ ^char]*直到char的第一次出现。

例如，要在第一个结肠之前删除所有文本，请使用此正则表达式：

模式： ^[ ^：]*：

为避免结果中的前导空间，请在末尾添加一个空格字符\ s*。这将在第一个结肠之前删除所有内容，并在其之后修剪任何空间：

模式： ^[ ^：]*：\ s*

=RegExpReplace(A5, "^[^:]*:\s*", "")

提示。除了正则表达式外，Excel还有自己的手段，可以按位置或匹配删除文本。要了解如何使用本机公式完成任务，请查看如何在Excel中的角色之前或之后删除文本。

以删除所有内容以外的一切

除了要保留的字符串之外，使用否定的字符类，从字符串中消除所有字符。

例如，要删除除小写字母和点以外的所有字符，正则是：

模式：[^az \。]

实际上，我们可以在此处没有量词的情况下做，因为我们的函数替代了所有发现的匹配。量词的速度更快 - 而不是处理每个字符，而是替换一个子字符串。

=RegExpReplace(A5, "[^az\.] ", "")

正则删除Excel中的HTML标签

首先，应该注意的是，HTML不是一种常规语言，因此使用正则表达式对其进行解析不是最好的方法。也就是说，Regexes绝对可以帮助从细胞中剥离标签，以使您的数据集更清洁。

鉴于始终将HTML标签放置在角度括号内，您可以使用以下言论之一找到它们。

否定课：

模式：]*>

在这里，我们匹配一个开头角括号，然后将任何字符的零或更多出现，除了闭合角括号[^>]*直至最近的闭合角括号。

懒惰搜索：

模式：

在这里，我们匹配了从第一个开放式支架到第一个关闭支架的任何内容。问号力。*要匹配尽可能少的字符，直到找到一个结束括号为止。

无论您选择哪种模式，结果将绝对相同。

例如，要从A5中的字符串中删除所有HTML标签并留下文本，该公式为：

=RegExpReplace(A5, "]*>", "")

或者，您可以使用屏幕截图中所示的懒惰量词：

该解决方案非常适合单文本（第5-9行）。对于多个文本（第10-12行），结果值得怀疑 - 来自不同标签的文本被合并为一个。这是正确的吗？恐怕，这不是很容易决定的东西 - 所有这些都取决于您对所需结果的理解。例如，在B11中，预期结果“ A1”。在B10中，您可能希望“ data1”和“ data2”与空间分开。

要删除HTML标签并将其余文本与空格分开，您可以以这种方式进行：

1. 用空格替换标签“”，而不是空字符串：

   =RegExpReplace(A5, "]*>", " ")

2. 将多个空间减少到单个空间字符：

   =RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " ")

3. 修剪领先和落后的空间：

   =TRIM(RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " "))

结果看起来像这样：

ablebits正则删除工具

如果您有机会使用我们的Ultimate Suite for Excel，那么您可能已经发现了最近版本中引入的新的Regex工具。这些基于.NET的正则函数的优点在于，首先，它们支持具有VBA REGEXP限制的全功能的正则表达语法，其次，不需要在您的工作簿中插入任何VBA代码，因为我们在后端的我们完成了所有代码集成。

您的工作部分是构建正则表达式并将其提供给功能:)让我向您展示如何在一个实际示例中做到这一点。

如何使用正则括号和括号中的括号和括号中的文字

在长文本字符串中，通常将较少重要的信息包含在[括号]和（括号）中。您如何删除保留所有其他数据的无关紧要的细节？

实际上，我们已经构建了一个类似的正则延期，用于删除HTML标签，即在角度括号内的文本。显然，相同的方法也适用于正方形和圆括号。

模式：（\（。*？\））|（\ [。*？\]）

诀窍是使用懒惰量词（*？）匹配最短可能的子字符串。第一组（\（。*？\））匹配从开口括号到第一个闭合括号。第二组（\ [。*？\]）匹配从开放式支架到第一个关闭括号。垂直条|充当或操作员。

通过确定模式，让我们将其“馈送”到我们的正则删除功能中。以下是：

1. 在“ ablebits数据”选项卡上，在文本组中，单击Regex工具。

   

2. 在Regex Tools Pane上，选择您的源字符串，输入您的正则拨号，选择“删除”选项，然后命中删除。

   要将结果作为公式，而不是值，请选择插入作为公式复选框。

   要从A2：A5中的字符串中删除括号中的文本，我们将设置配置如下：

   

结果，在您的原始数据旁边的新列中插入了Ablebitsregexremove功能。

该函数也可以通过标准插入功能对话框直接输入在单元格中，该函数在ablebitsudfs下对其进行分类。

由于AbleBitsRegeXremove旨在删除文本，因此仅需要两个参数 - 源字符串和正则是正则。这两个参数都可以直接以公式定义，也可以以单元参考的形式提供。如果需要，可以将此自定义功能与任何本地功能一起使用。

例如，要修剪所得字符串中的额外空间，您可以将装饰函数用作包装器：

=TRIM(AblebitsRegexRemove(A5, $A$2))

这就是如何使用正则表达式删除Excel中的字符串的方法。我感谢您阅读，并期待下周在我们的博客上与您见面！

可用下载

使用REGEX删除字符串 - 示例（.xlsm File）Ultimate Suite-试用版（.EXE文件）

以上是以删除Excel中的某些字符或文字的正则的详细内容。更多信息请关注PHP中文网其他相关文章！