excel - 使用VBA替换字符串中特定位置的文本

Question

我想通过指定 substring_a 的位置和长度，用字符串中的另一个子字符串 (substring_b) 替换一个子字符串 (substring_a)。

我正在尝试使用 Mid 函数，但它似乎不起作用，除非 substring_b 等于或长于 substring_a

这是我的代码：

Sub Test_Mid()
Dim starting_text As String
Dim replacement_text As String

starting_text = "Long Starting Text"
replacement_text = "End"

Mid(starting_text, 6, 8) = replacement_text
MsgBox (starting_text)

End Sub

我希望代码返回“Long End Text”，但它返回“Long Endrting Text”。

我该如何解决？

注：以上为说明性示例。我不能直接将 Replace 函数与 replacement_text 一起使用，因为在我的实际宏中，replacement_text 是可变的。

标签： excelvba

Answer 1

You are making a simple thing overly complex. All that is needed is a simple replace.

starting_text = Replace(starting_text, "Starting", "End")

To replace just the 1st occurrence, use this:

starting_text = Replace(starting_text, "Starting", "End", 1, 1)

Or if it's just one occurrence, starting at a specific location, use this:

starting_text = Replace(starting_text, "Starting", "End", Instr(starting_text, "Starting"), 1)

Or if it's just one occurrence, starting at a specific location, and you dont want it to be case sensitive, use this:

starting_text = Replace(starting_text, "Starting", "End", Instr(starting_text, "Starting", vbTextCompare), 1, vbTextCompare)

VBA Replace Function