Excel VBA Replace the nth word in a string

Question

my problem is the following:

I have two sets of strings. The "words" are separated with "+".

String 1: A25+F47+w41+r21+h65

String 2: 4+7+4+4+2

I have a textbox that identifies the word “w41” in string 1. It is the 3rd word in the string. I want to replace the 3rd word in string 2 and that would be the second “4”

What I have so far is:

I am using split function to split string 1 where there is a “+”:

Result=Split(String1, "+")

Then I use the UBound to find the position of w41 and the result is 3.

FindWordPosition = UBound(Result()) + 1

Now I want to split string 2 in the same way. But then I want to change the 3rd word in string 2 from “4” to “3” and then put it together again. The result would be:

String 2: 4+7+3+4+2

but I cannot figure out how to do it :(

question from:https://stackoverflow.com/questions/65925943/excel-vba-replace-the-nth-word-in-a-string

Answer 1

One way is to use an ArrayList.

Not sure what you want to replace the matched item with. In the code below, it is being replaced with the sequence number, which matches what you describe, but may not be what you really want.

Option Explicit
Sub due()
    Const str1 As String = "A25+F47+w41+r21+h65"
    Const str2 As String = "4+7+4+4+2"
    Const strMatch As String = "w41"
    Dim AL As Object
    Dim v, w, I As Long
    
Set AL = CreateObject("System.Collections.ArrayList")

'put str2 into arrayList
v = Split(str2, "+")
For Each w In v
    AL.Add w
Next w

'Check str1 against matchStr to get positions and act on the item in AL at that position
v = Split(str1, "+")
For I = 0 To UBound(v)
'Note that arrayList index and "Split" array are zero-based
    If strMatch = v(I) Then
        AL.removeat I 'remove item in the same position as the position of the matched item
        AL.Insert I, I + 1 'Insert new item at that same position. Could be anything. I chose I+1 to match what you wrote in your question.
    End If
Next I

Debug.Print Join(AL.toarray, "+")
    
End Sub

=> 4+7+3+4+2