如何在Excel中使用正则表达式查找和替换

Question

我有一个包含一列和多行的Excel文件。

这些行包含各种文本，以下是一个例子:

texts are home
texts are whatever
dafds
dgretwer
werweerqwr
texts are 21412
texts are 346345
texts are rwefdg
terfesfasd
rwerw

我想用一个特定的单词替换“texts are *”，其中*是在“texts are”之后的任何内容，例如“texts are replaced”。我该如何在Excel中实现这个目标？

- user5796570

---

请参阅Office支持网站https://support.office.com/en-us/article/Find-and-replace-text-by-using-regular-expressions-Advanced-eeaa03b0-e9f3-4921-b1e8-85b0ad1c427f。 - Adam Axtmann

我已经阅读了那篇文章，尝试了一些示例并且制作了自己的正则表达式，但什么也没有返回。我是做错了什么吗？ - user5796570

你使用的 Excel 版本是什么？我使用的是2016年版本，我刚刚按照“通配符替换项”部分的步骤，在使用您上面提供的示例后，成功地找到了“文字是*”并用“文字已替换”替换了所有实例，结果正如预期的那样，除非我误解了。 - Adam Axtmann

@user5796570，我该怎么使用这个？我需要将上面的代码复制粘贴到一个单元格中，并在前面加上等于号“=”作为公式吗？ - YouHaveaBigEgo

@AdamAxtmann 这篇文章是针对Word的，不适用于Excel 2016。 - not2qubit

Answer 1

使用 Google Sheets 而不是 Excel - 这个功能已经内置了，因此您可以直接从查找和替换对话框中使用正则表达式。

回答您的问题：

1. 将数据从 Excel 复制并粘贴到 Google Sheets 中
2. 使用带有正则表达式的查找和替换对话框
3. 将数据从 Google Sheets 复制并粘贴回 Excel 中

- Crag

---

另外，您可以使用支持正则表达式的LibreOffice。 - Luca

问题明确地说“我如何在Excel中做到这一点？” - Mohsen

对于我的使用情况来说太慢了。页面在处理大小为15,000行乘以9列的数据时冻结了一分钟或更长时间。 - Colm Bhandal

@Mohsen 我认为答案是在Excel中无法使用正则表达式完成。 - Leon

Answer 2

如果你想要一个公式来完成它，则：

=IF(ISNUMBER(SEARCH("*texts are *",A1)),LEFT(A1,FIND("texts are ",A1) + 9) & "WORD",A1)

这将解决问题。将 "WORD" 更改为您想要的单词。

- Scott Craner

---

这个答案缺少教育性的解释。请[编辑]这个答案来解释这个表达式的工作原理。我相信对于经常使用Excel的人来说，这个答案是“不言自明”的，但对于非经常使用者来说，这一堆命令可能会难以理解。 - mickmackusa

Answer 3

现在是2021年，你可以使用Excel的替换功能。

• 关键点：
  • 查找：texts are *
  • 替换为：texts are replaced
• 步骤：
  • 选择要替换的内容，（右上角）选择替换
    • 
  • 在弹出的替换窗口中，输入查找和替换规则
    • 
  • 点击全部替换，完成 ^_^
    • 

- crifan

---

在你的回答中混合两种不同的语言对读者是有害的。 - Pearce

谢谢您提供的中文截图 :) - TheTechGuy

你是认真的吗？)) 你想要一个带阿拉伯字母的答案吗？:)) - serge

Answer 4

显然，Excel 不使用正则表达式，但有一个解决方法。 您可以在搜索模式中使用 *、? 或 ~。

要查找：

? (question mark) =  Any single character. For example, sm?th finds "smith" and "smyth"  

* (asterisk) = Any number of characters For example, *east finds "Northeast" and "Southeast"  

~ (tilde) followed by ?, *, or ~ = A question mark, asterisk, or tilde. For example, fy06~? finds "fy06?"

你可以使用这些组合来获得类似于正则表达式的模式。

- Hammad Khan

---

你能限制字符数量吗？?可以找到单个字符，而*可以找到无限数量的字符。但如果你想要找到无限数量的字符怎么办？此外，如何使用分组来重新填充在查找中找到的某些字符（就像在正则表达式中一样）？ - Jon Grah

您可以使用“??”来替换两个字符，“????”来替换四个字符。对于无限数量的字符，您可以使用“*”，但这实际上会匹配到字符串结尾的所有内容。 - TheTechGuy

其他用户是正确的。Excel并不适合正确地执行正则表达式搜索。最好将公式复制到外部文本编辑器（如notepad++）中（查找/替换= 为/= ；将公式复制到notepad++中），然后在那里进行所有查找/替换/正则表达式更改（保持选项卡分隔符不变）。然后将所有内容复制回Excel中，再将/= 替换为 =。 - Jon Grah

值得注意的是，这个帮助内容确实存在，但是没有人注意到。 :-) - Antonio

Answer 5

作为正则表达式的替代，运行以下代码：

Sub Replacer()
   Dim N As Long, i As Long
   N = Cells(Rows.Count, "A").End(xlUp).Row

   For i = 1 To N
      If Left(Cells(i, "A").Value, 9) = "texts are" Then
         Cells(i, "A").Value = "texts are replaced"
      End If
   Next i
End Sub

将会产生：

- Gary's Student

Answer 6

你可以...

1. 退出Excel
2. 复制你的Excel文件并添加扩展名（例如.xls或.xlsm）.xml.zip。例如，test.xls变成test.xls.xml.zip。
3. 解压缩.zip文件。是的，Excel文件实际上是.zip文件。

这将给你一个名为test.xls.xml（或test.xlsm.xml）的目录，其中包含以下目录结构：\xl\worksheets。

在worksheets目录中是你的Excel工作表，以XML格式存储。它们没有进行可读性格式化，所以使用一个能够对XML进行“漂亮”格式化的编辑器（我使用EditPlus或XMLSpy，市面上有很多选择）。现在你可以阅读XML，并使用编辑器的正则表达式功能进行更改。

完成后，进入test.xls.xml（或test.xlsm.xml）目录。您将看到_rels、docProps和xl子目录以及[Content_Types].xml文件。选择所有内容，右键单击并选择发送到>压缩文件夹(.zip)。确保您的文本编辑器中没有剩余的.bak文件（在worksheets子目录中）。如果有，请在创建新的.zip文件之前将它们删除。现在将此新的.zip文件的扩展名改为.xls（或.xlsm）。

现在您可以在Excel中打开此文件。Excel不会介意您已经“漂亮”地格式化了XML。

我已经执行过这个精确的过程很多次，从来没有出现任何问题（除非我不小心留下了.bak文件）。

一个警告，只包含文本（即没有公式）的Excel单元格将文本存储在不同的文件中，并从工作表文件中引用它们，因此您在那里看不到该文本。任何包含在公式中的文本（即CONCAT("ABC", "DEF")）都会保留在工作表中。

- mbmast

---

非常精确但有效！ - mohsyn