VBScript Replace for character string of unknown length

Question

I am working in Excel to take a faulty URL and replace the incorrect characters. The characters I want to replace are "%20" BUT there is no way to know how many of those characters will be in the string. There might be just one "%20" or 5 "%20%20%20%20%20" or any other number of them in between but no matter how many "%20" characters there are I want to replace with only ONE forward slash "/".

I know I can use something like below, but don't want to end up with more than one forward slash if there are more than one "%20"

Sub fixURL()

Worksheets("Google Feed Original").Activate

 Worksheets("Google Feed Original").Columns("N").Replace _
        What:="%20", replacement:="/", LookAt:=xlPart

End Sub

For example the starting URL might look like:

http://domainname.com/products/prodName%20%20%20ProdNumber

and no matter how many "%20" are in the middle, I am trying to get it to format like this instead:

http://domainname.com/products/prodName/ProdNumber

Is there some way I can replace all of them with just one "/" no matter how many "%20" are in the string?

Thanks.

score 2 · Answer 1 · answered Jun 05 '17 at 19:35

2

Use a regular expression (start here) to replace sequences of the string "%20" with one "/":

>> s = "http://domainname.com/products/prodName%20%20%20ProdNumber"
>> Set r = New RegExp
>> r.Global = True
>> r.Pattern = "(%20)+"
>> WScript.Echo s
>> WScript.Echo r.Replace(s, "/")
>>
http://domainname.com/products/prodName%20%20%20ProdNumber
http://domainname.com/products/prodName/ProdNumber
>>

answered Jun 05 '17 at 19:35

Ekkehard.Horner

38,498
2
45
96

Thanks, I am looking at this but realizing I might run into an issue since this is on a Mac :( Trying to determine if I can use the RegExp on Mac first. – waterwalk77 Jun 05 '17 at 20:06
Unable to do regular expression on Mac so I am going to have to try this a different way. Thanks for responding though. – waterwalk77 Jun 05 '17 at 23:54

Gurmanjot Singh · Answer 2 · 2017-06-06T06:29:04.903

The following code will replace all the consecutive %20 with a single /. It does not make use of any regular expression. It first splits the string using the delimiter %20 and then if the length of each of the array element is greater than 0, it joins them with the delimiter /.

str = "http://domainname.com/products/prodName%20%20%20ProdNumber"
a=Split(str,"%20")
For i=0 To UBound(a)
    If Len(a(i))>0 Then
        requiredStr = requiredStr & a(i)&"/"
    End If
Next
requiredStr=Left(requiredStr,Len(requiredStr)-1)
MsgBox requiredStr

Let me know if it works for you.

Output:

score -1 · Answer 3 · answered Jun 05 '17 at 19:17

-1

You could write a recursive function that will perform the Replace call so long as there are duplicate instances of the / character. It has been years since I have written VBScript, but I do not remember a function to do that type of replacement built into the language.

answered Jun 05 '17 at 19:17

Incorporeal Logic

280
3
17

Another interesting idea. Not sure how to code this yet but will look at this too. Thx. – waterwalk77 Jun 05 '17 at 20:07

VBScript Replace for character string of unknown length

3 Answers3