vba 如何使用格式化文本查找和替换单元格的一部分

How to find and replace part of a cell with formatted text

提问by Sam

In an Excel 2007 spreadsheet I want to find-replace with highlighting part of the text in a cell. Using find-replace reformats the entire cell though.

在 Excel 2007 电子表格中，我想通过突出显示单元格中的部分文本进行查找替换。使用查找替换重新格式化整个单元格。

For example, if the cell contains:

例如，如果单元格包含：

Pellentesque vel massa sit amet magna eleifend placerat. Pellentesque dictum, nibh vitae tincidunt placerat, elit libero tristique tellus, vel imperdiet nulla tortor id diam. Mauris porta blandit vestibulum.

Pellentesque vel massa 坐在 amet magna eleifend placerat。Pellentesque dictum，nibh vitae tincidunt placerat，elit libero tristiquetellus，vel imperdiet nulla tortor id diam。Mauris porta blandit 前庭。

I want to find "Pellentesque" and replace it with Pellentesque.

我想找到“Pellentesque”并将其替换为Pellentesque。

Can this be done without VBE or formulas?

这可以在没有 VBE 或公式的情况下完成吗？

回答by pnuts

Maybe this would suit (be sure to select no more cells than necessary before running or this could take a while):

也许这适合（确保在运行之前选择的单元格不超过必要的数量，否则这可能需要一段时间）：

Sub FormatSelection()

Dim cl As Range
Dim SearchText As String
Dim StartPos As Integer
Dim EndPos As Integer
Dim TestPos As Integer
Dim TotalLen As Integer

On Error Resume Next
Application.DisplayAlerts = False
SearchText = Application.InputBox _
(Prompt:="Enter string.", Title:="Which string to format?", Type:=2)
On Error GoTo 0
Application.DisplayAlerts = True
If SearchText = "" Then
Exit Sub
Else
For Each cl In Selection
  TotalLen = Len(SearchText)
  StartPos = InStr(cl, SearchText)
  TestPos = 0
  Do While StartPos > TestPos
    With cl.Characters(StartPos, TotalLen).Font
      .FontStyle = "Bold"
      .ColorIndex = 3
    End With
    EndPos = StartPos + TotalLen
    TestPos = TestPos + EndPos
    StartPos = InStr(TestPos, cl, SearchText, vbTextCompare)
  Loop
Next cl
End If
End Sub

Should embolden and colour Red. Changes are not overwritten if macro is rerun. Comment out .ColorIndex = 3 if not to change colour.

应该大胆和颜色红色。如果重新运行宏，更改不会被覆盖。如果不改变颜色，注释掉 .ColorIndex = 3。

(Based on @Skip Intro's pointer to SO15438731 question with amendment and some code from SO10455366 answer.)

（基于@Skip Intro 指向 SO15438731 问题的指针，并进行了修正，以及 SO10455366 答案中的一些代码。）

回答by Charlie Webster

This is what worked great for me:

这对我来说很有用：

1. Selected cells in Excel (I selected 1266 at a time--don't know if there's really any limit)

2. Clicked on COPY

3. Opened a blank word file and clicked on PASTE

4. Used CNTL+H to get Find & Replace

5. Entered the characters to find and the characters to replace, adding any formatting desired and selected Replace All (the process took less than 2 seconds)

6. Selected Edit-Select-Select All in the Word file, then held the shift key and pressed the left arrow (I think not doing this would add a row to the next operation)

7. Went back into Excel and, in a spare area, selected Paste - Keep Source Formatting option

1. Excel中选中的单元格（我一次选了1266个——不知道是不是真的有限制）

2. 点击复制

3. 打开一个空白的word文件并点击PASTE

4. 使用 CNTL+H 进行查找和替换

5. 输入要查找的字符和要替换的字符，添加所需的任何格式并选择全部替换（该过程不到 2 秒）

6. 在Word文件中选中Edit-Select-Select All，然后按住shift键并按左箭头（我认为不这样做会在下一个操作中添加一行）

7. 回到 Excel 并在备用区域中选择粘贴 - 保留源格式选项

That put exactly what I wanted into Excel. From there I could move it wherever I wanted it.

这正是我想要的 Excel。从那里我可以将它移动到任何我想要的地方。

回答by Bro_Marko

The absolute EASIEST WAY to do this with the standard Find & Replace CTRL+F Command is to just "temporary" add-in the "Calibri Font" Character ? aka. "black bullet dot"

使用标准查找和替换 CTRL+F 命令执行此操作的绝对最简单方法是“临时”添加“Calibri 字体”字符？又名。“黑色子弹点”

like this: ? sample ?

像这样： ？样本 ？

from the Insert Tab --> Symbols --> Calibri Font --> and at the very end)

从插入选项卡 --> 符号 --> Calibri 字体 --> 和最后）

Thus you can make it much easier to the eyes to read over many rows of excel data,

因此，您可以使眼睛更容易阅读多行 excel 数据，

and easily also later remove it back again to the original with simply again a Find & Replace reversed.

并且稍后也可以轻松地将其再次删除回原来的位置，只需再次反向查找和替换即可。

Hope this helps someone, it sure helped my eyes to scan over quicker.

希望这可以帮助某人，它确实帮助我的眼睛更快地扫描。

---

---

OLD RESPONSE

旧回应

the easy non-programming way to do this, is to:

做到这一点的简单非编程方法是：

1. paste the text into MS Word

2. Then run the Find & Replace (CTRL+F) there (with the custom formatting changes at the bottom left button under "More > >" and then "Format" to your need customized) - which will then do all the changes perfectly...

3. and then rather than doing a PASTE SPECIAL into Excel (via the Home Tab --> Paste --> Paste Special --> Paste as HTML (so to retain the formatting) (which can and is too memory intensive and too slow for anything above like 1000 rows)

1. 将文本粘贴到 MS Word 中

2. 然后在那里运行查找和替换（CTRL + F）（在“更多> >”下的左下角按钮处进行自定义格式更改，然后根据您的需要自定义“格式”） - 这将完美地完成所有更改.. .

3. 然后，而不是在 Excel 中执行 PASTE SPECIAL（通过主页选项卡 --> 粘贴 --> 选择性粘贴 --> 粘贴为 HTML（以便保留格式）（这可能并且太占用内存并且对于任何事情来说都太慢）上面像 1000 行）

it is better to simply "Save as" from Word the document into a RTF-File, and then just re-import that file into Excel via the simple "Open File".

最好将 Word 文档中的“另存为”简单地“另存为”到 RTF 文件中，然后通过简单的“打开文件”将该文件重新导入 Excel。

Excel has a pretty good "repeating pattern cell structuring" recognition, but of course, this workaround may not work perfectly in reproducing the desired cell division every single time, but it should maintain the cell order nevertheless most of the time.

Excel 具有非常好的“重复模式单元格结构”识别功能，但当然，这种解决方法可能无法完美地再现所需的单元格分裂，但在大多数情况下它仍应保持单元格顺序。

So much for the simple way to do it, until Microsoft stops limiting an option to "highlight/bold" a found-result-text WITHIN a cell, rather than always bolding the entire cell.

简单的方法就到此为止，直到 Microsoft 停止将选项限制为在单元格内“突出显示/加粗”找到的结果文本，而不是始终将整个单元格加粗。

Greetings Marko

问候马可

回答by John Lam

To make the search item case agnostic you can use:

要使搜索项大小写不可知，您可以使用：

Sub FormatSelection()

Dim cl As Range
Dim SearchText As String
Dim StartPos As Integer
Dim EndPos As Integer
Dim TestPos As Integer
Dim TotalLen As Integer

On Error Resume Next
Application.DisplayAlerts = False
SearchText = Application.InputBox _
(Prompt:="Enter string.", Title:="Which string to format?", Type:=2)
On Error GoTo 0
Application.DisplayAlerts = True
If SearchText = "" Then
Exit Sub
Else
For Each cl In Selection
  TotalLen = Len(SearchText)
  StartPos = InStr(Ucase(cl), Ucase(SearchText))
  TestPos = 0
  Do While StartPos > TestPos
    With cl.Characters(StartPos, TotalLen).Font
      .FontStyle = "Bold"
      .ColorIndex = 3
    End With
    EndPos = StartPos + TotalLen
    TestPos = TestPos + EndPos
    StartPos = InStr(TestPos, cl, SearchText, vbTextCompare)
  Loop
Next cl
End If
End Sub