仅更改单元格中字符串的特定部分，保持原样 [英] Change only a specific part of a string in a cell, keep rest as it is

问题描述

从单元格中取值，如果单元格中的show替换为change。
单元格具有多于一种颜色，我必须保留所有单词颜色（除了显示）。

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!

推荐答案

这是一个示例代码，更改文本的其他部分的当前颜色或其他字体属性。我将您的示例文本字符串与字体颜色一起显示到单元格A1中。

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

注意字符的重要方面（）.Insert（）行。字符（开始，长度）是要删除的部分和大小，插入将新的（和更长）的文本放在其位置。

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.

这篇关于仅更改单元格中字符串的特定部分，保持原样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！