Replace text at specific position in string using VBA

Question

I want to replace a substring (substring_a) with another substring (substring_b) within a string by specifying the position and length of substring_a.

I'm trying to use the Mid function, but it doesn't seem to work unless substring_b is equal to or longer than substring_a

Here is my code:

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

I want the code to return "Long End Text", but instead it returns "Long Endrting Text".

How do I fix this?

Note: The above is an illustrative example. I can't use the Replace function directly with the replacement_text because in my actual macro, replacement_text is variable.

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

braX
– braX

2019-11-08 16:55:00 +00:00
Commented Nov 8, 2019 at 16:55 — braX
– braX, Commented Nov 8, 2019 at 16:55

braX · Accepted Answer · 2019-11-08 17:37:21Z

5

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

edited Nov 8, 2019 at 17:37

answered Nov 8, 2019 at 17:04

braX

11.9k5 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user7758051 Over a year ago

In my actual use case, the replacement_text is variable, where the starting and ending characters are standard, but the characters inbetween those are all different. I was previously told that the Replace function doesn't handle wildcard characters (ie "*") in VBA, so I can't use the Replace function for this. I'll update my original question to clarify this.

braX Over a year ago

it can be a variable too - starting_text = Replace(starting_text, "Starting", replacement_text) - or if it's a function - starting_text = Replace(starting_text, "Starting", replacement_text())

Sandra Rossi · Accepted Answer · 2023-07-26 15:30:35Z

1

This is a ready-to-use function to replace the exact positions given by Start and Length parameters:

Function ReplacePositions(Expression As String, Start As Integer, Length As Integer, Replace As String) As String
  ReplacePositions = Mid(String:=Expression, Start:=1, Length:=Start - 1) _
                   & Replace _
                   & Mid(String:=Expression, Start:=Start + Length)
End Function

This will give:

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

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

starting_text = ReplacePositions(starting_text, 6, 8, replacement_text)
MsgBox (starting_text) ' will show "Long End Text"

End Sub

answered Jul 26, 2023 at 15:30

Sandra Rossi

14k6 gold badges28 silver badges58 bronze badges

Comments

user7758051 · Accepted Answer · 2019-11-08 19:06:02Z

0

Below is my solution:

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

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

replaced_text = Mid(starting_text, 6, 8)
starting_text = Replace(starting_text, replaced_text, replacement_text)

MsgBox (starting_text)

End Sub

Note: For the example code here, @brax's solution is more efficient, but this code above shows how to replace text at a specific position (which is what I need for my actual use case).

answered Nov 8, 2019 at 19:06

user7758051

3642 gold badges7 silver badges18 bronze badges

1 Comment

Vityata Over a year ago

My solution uses Mid() as well, but without the hardcoding of 6 and 8.

Vityata · Accepted Answer · 2019-11-08 17:48:59Z

Replacing a string in string in VBA is an easy taks, if Replace() is used. Anyway, if Replace() is to be omitted, then it becomes more interesting. Theoretically, the string could be splitted always into 3 parts:

First Part - String To Replace - Last Part

Even when the FirstPart or/and the LastPart are with length 0, the division is to be made. Then, removing StringToReplace from the string completely and in its position, write the new string. The position is determinted with IsStr. It could be used as a function, using error handlers to return the initial expression, if the find is not part of the string:

Function Replace2(expression As String, find As String, replace As String) As String

    On Error GoTo replace2_Error

    Replace2 = expression        
    Dim position As Long: position = InStr(1, expression, find)
    Replace2 = Left(expression, position - 1)    
    Replace2 = Replace2 & Mid(expression, position + Len(find))
    Replace2 = Left(Replace2, position - 1) & _
                    replace & _
                    Right(Replace2, Len(Replace2) - position + 1)

    Exit Function

replace2_Error:
    Replace2 = expression    
End Function

And the tests of the function:

Sub TestMe()

    Debug.Print Replace2("1", "", "")
    Debug.Print Replace2("1", "", "2")
    Debug.Print Replace2("", "3", "")
    Debug.Print Replace2("32", "a", "")
    Debug.Print Replace2("My Very Interesting Long Starting Text", "Starting", "Ending")
    Debug.Print Replace2("My Very Interesting Long Text Starting", "Starting", "Ending")
    Debug.Print Replace2("Starting My Very Interesting Long Text", "Starting", "Ending")

End Sub

Collectives™ on Stack Overflow

Replace text at specific position in string using VBA

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related