VBA Find and replace if starts with and ends with

[Klturi421]

I have a very unique document that I am working to format that includes a string of text such as ${e://file/_X1236}. and each time a similar string appears, the number is different. For example there might be ${e://file/_X9561}. There are easily 200+ entries each with different numbers otherwise I would call each one individually.

Essentially, I am looking to have a script that can find the string that starts with ${ and ends with }. (period included). Once found, the script should replace it with "Global Question:".

For example:
${e://file/_X1236}. How are you today? => Global Question: How are you today?
${e://file/_X9561}. What is your favorite color? => Global Question: What is your favorite color?


I've seen plenty of scripts that can find the start or the end but nothing so far that does both.

Any ideas?

 

[jolivanes]

With such a small data range, looping should be sufficient.

Sub Maybe()
Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Left(c, 2) = "${" And Mid(c, 18, 2) = "}." Then c.Value = "Global Question: " & Mid(c, 21, 256)
    Next c
End Sub

 

[BSALV]

i don't know where your "global question" has to come, so a guess.

Sub test()
     s = "I have a very unique document that I am working to format that includes a string of text such as ${e://file/_X1236}. and each time a similar string appears, the number is different. For example there might be ${e://file/_X9561}. There are easily 200+ entries each with different numbers otherwise I would call each one individually. Essentially, I am looking to have a script that can find the string that starts with ${ and ends with }. (period included). Once found, the script should replace it with Global Question: For example: ${e://file/_X1236}. How are you today? => Global Question: How are you today? ${e://file/_X9561}. What is your favorite color? => Global Question: What is your favorite color?"

     sp = Split(Replace(Replace(s, "${", "|" & "${"), "}", "}." & "|"), "|")     'add a unused character just in front and behind that special text
     For i = 0 To UBound(sp)                                    'loop through the parts
          If Left(sp(i), 2) = "${" And Right(sp(i), 2) = "}." Then sp(i) = sp(i) & " Global Question : "     'if starts and ends with the "${" and "}.", then add "Global Question"
     Next

     s1 = Join(sp, "")                                          'join the parts

     Range("A1") = s                                            'original string
     Range("A2") = s1                                           'modified string
     'Range("A3") = Join(sp, vbLf)

End Sub

 

[Rick Rothstein]

I think this should work...

Sub TryThis()
  Columns("A").Replace "${*}.", "Global Question:", xlPart, , , , False, False
End Sub

 

[Klturi421]

Thank you, Rick! It worked very well. I appreciate that.