如何使用格式化文本查找和替换单元格的一部分 [英] How to find and replace part of a cell with formatted text

问题描述

在Excel 2007电子表格中，我要查找 - 替换单元格中突出部分的文本。使用find-replace格式化整个单元格。

例如，如果单元格包含：

Pellentesque vel massa sit amet magna eleifend placerat。 Pellentesque
dictum，nibh vitae tincidunt placerat，elit libero tristique tellus，
vel imperdiet nulla tortor id diam。

我想找到Pellentesque，并将其替换为 Pellentesque 。 p>

这可以不用VBE还是公式？

解决方案

（请确保在运行前不要再选择所需的单元格，否则可能需要一段时间）：

  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 _ 
（提示符：=输入字符串，标题：=要格式化的字符串，类型：= 2）
错误GoTo 0 
 Application.DisplayAlerts = True 
如果SearchText =然后
退出Sub 
 Else 
对于每个cl In Selection 
 TotalLen = Len（SearchText）
 StartPo s = InStr（cl，SearchText）
 TestPos = 0 
尽管StartPos> TestPos 
与cl.Characters（StartPos，TotalLen）.Font 
 .FontStyle =Bold
 .ColorIndex = 3 
结束
 EndPos = StartPos + TotalLen 
 TestPos = TestPos + EndPos 
 StartPos = InStr（TestPos，cl，SearchText，vbTextCompare）
循环
下一个cl 
如果
 End Sub 
  

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

（基于@Skip Intro的指向SO15438731问题的修正和一些代码来自SO10455366的答案。）

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.

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.

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

Can this be done without VBE or formulas?

解决方案

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.

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

这篇关于如何使用格式化文本查找和替换单元格的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！