CODEWITHSUNDEEP
excelvbareplace
user1564762
user1564762

Reputation: 825

Change only a specific part of a string in a cell, keep rest as it is

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”

enter image description here

' 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!

Upvotes: 1

Views: 1339

Answers (1)

cybermike
cybermike

Reputation: 1147

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.

Upvotes: 3

Related Questions