CODEWITHSUNDEEP
vbscript
waterwalk77
waterwalk77

Reputation: 48

VBScript Replace for character string of unknown length

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.

Upvotes: 2

Views: 787

Answers (3)

Gurmanjot Singh
Gurmanjot Singh

Reputation: 10360

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: enter image description here

Upvotes: 1

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38775

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
>>

Upvotes: 2

Incorporeal Logic
Incorporeal Logic

Reputation: 280

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.

Upvotes: -1

Related Questions