Geek Answers Handbook

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

In an Excel 2007 spreadsheet, I want to find-replace by highlighting part of the text in a cell. Using find-replace will reformat the whole cell.

For example, if a cell contains:

Pellentesque vel massa sit amet magna eleifend placerat. Pellentesque dictum, nibh vitae tincidunt placerat, elit libero tristique tellus, vel optional 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?

+3
source share
4 answers

, ( , , , ):

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

. , . .ColorIndex = 3, .

( @Skip Intro SO15438731 SO10455366 .)

+5

, :

  • Excel ( 1266 - , - )

  • COPY

  • PASTE

  • CNTL + H

  • , , , , . ( 2 ).

  • Edit-Select-Select All Word, shift ( , )

  • Excel " - "

, Excel. , .

+2

, " CTRL + F" "" " " • . " "

: • •

→ → → )

, excel,

" ".

, -, , , .

OLD RESPONSE

, , :

  • MS Word

  • Find Replace (CTRL + F) ( " → ", "" ), ...

  • , PASTE SPECIAL Excel ( "" → "" → " " → "" HTML ( ) ( - , 1000 )

" " Word RTF , Excel "Open File".

Excel " ", , , , , , .

, Microsoft "/" , .

Marko

+1

, :

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
0

More articles:


All Articles