Replacing number followed by word from string using Excel VBA

[mathsbeauty]

There are numbers (1 to 4 digit numbers < 1000) followed by the word "Page" in a string. I want to replace the numbers appearing immediately after the word "Page" by "xyz". So the resulting string should be as "abcd Page xyz dfdfsda Page xyz fsadfds Page xyz" when the original string is "abcd Page 32 dfdfsda Page 1 fsadfds Page 4567". It would be nice if we can construct a function in Excel VBA for the same. If replacement is not possible, then removing numbers would also be okay.

 

[mohadin]

Hi
?

Sub test()
Dim txt, x, t, i
   txt = "abcd Page 32 dfdfsda Page 1 fsadfds Page 4567"
    With CreateObject("VBScript.RegExp")
        .Pattern = "(\D+Page )"
        .Global = True
            Set x = .Execute(txt)
        For i = 0 To x.Count - 1
        t = t & x(i) & " XYZ "
        Next
    End With
    MsgBox t
End Sub

 

[Mescoman]

Ahhh mohadin beat me to it. Ill just post my idea anyways since I spent some time on it and it might still be helpful
My idea:

For i = 0 to 9, replace "Page i" with "Page xyz"
Then, 3 times after this, do the following:
For i = 0 to 9, replace "Page xyzi" with "Page xyz" (note the purposeful spaces).

Sorry for not actually coding, I am quite new with VBA, but on paper the pseudocode should work.

 

[mohadin]

And

Sub test2()
  
Sub test2()
    Dim txt, x, t, i
    txt = "abcd Page 32 dfdfsda Page 1 fsadfds Page 4567"
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d{1,4}"
        .Global = True
      MsgBox .Replace(txt, " XYZ ")
    End With
End Sub

 

[FormR]

Hi, here's another option you could also try.

Function myReplace(s As String)
Dim v As Variant, i As Long
v = Split(s, " ")
For i = 1 To UBound(v)
    If v(i) = "Page" Then
        If 1 < UBound(v) Then
            If IsNumeric(v(i + 1)) Then v(i + 1) = "xyz"
        End If
    End If
Next i
myReplace = Join(v, " ")
End Function

Book1
	A	B
1	abcd Page 32 dfdfsda Page 1 fsadfds Page 4567	abcd Page xyz dfdfsda Page xyz fsadfds Page xyz
2	abcd Page 32 dfdfsda Page 1 fsadfds Page 4567 page	abcd Page xyz dfdfsda Page xyz fsadfds Page xyz page
3	abcd Page 32 dfdfsda Page abc fsadfds Page 4567 page	abcd Page xyz dfdfsda Page abc fsadfds Page xyz page
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=myReplace(A1)

 

[mathsbeauty]

Hi, here's another option you could also try.

Function myReplace(s As String)
Dim v As Variant, i As Long
v = Split(s, " ")
For i = 1 To UBound(v)
    If v(i) = "Page" Then
        If 1 < UBound(v) Then
            If IsNumeric(v(i + 1)) Then v(i + 1) = "xyz"
        End If
    End If
Next i
myReplace = Join(v, " ")
End Function

Book1
	A	B
1	abcd Page 32 dfdfsda Page 1 fsadfds Page 4567	abcd Page xyz dfdfsda Page xyz fsadfds Page xyz
2	abcd Page 32 dfdfsda Page 1 fsadfds Page 4567 page	abcd Page xyz dfdfsda Page xyz fsadfds Page xyz page
3	abcd Page 32 dfdfsda Page abc fsadfds Page 4567 page	abcd Page xyz dfdfsda Page abc fsadfds Page xyz page
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=myReplace(A1)

Thanks. It worked for my purpose.

 

[FormR]

Hi, sorry - I just noticed a couple of typos in my code, you should use this instead.

Function myReplace(s As String)
Dim v As Variant, i As Long
v = Split(s, " ")
For i = 0 To UBound(v)
    If v(i) = "Page" Then
        If i < UBound(v) Then
            If IsNumeric(v(i + 1)) Then v(i + 1) = "xyz"
        End If
    End If
Next i
myReplace = Join(v, " ")
End Function