Change only a specific part of a string in a cell,

问题:

Take a value from a cell, if "show" is in the cell replace it with "change". The cell have more then one color, I must keep all words color (except for show).

text = Cells(row, col).value
' text have other colors then black – and I want to keep that
text = “hi, this test show how the text can look like”

' want to replace “show” to “change” and keep the text coloring.
' similarly I can set the color with
Cells(row, col).Characters(15, Len(show)).Font.Color = vbBlue
' Can I change the text in a similar way?
' If I use:
Cells(row, col).value = text
' All color is gone!

回答1:

Here is a sample code that will change the text without altering the current color or other font attributes of other portions of the text. I put your sample text string with font colors as shown into cell A1.

Public Sub test()
  Dim str As String, pos As Long
  str = Range("A1").Value
  pos = InStr(str, "show")
  Range("A1").Characters(pos, 4).Insert ("change")
End Sub

Note the important aspect of the Characters().Insert() line. The Characters(start, length) is the portion AND SIZE that you want to remove, and the Insert puts the new (and longer) text in its place.