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.

Ekkehard.Horner · Accepted Answer · 2017-06-05 19:35:00Z

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 5, 2017 at 19:35

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

waterwalk77 Over a year ago

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 Over a year ago

Unable to do regular expression on Mac so I am going to have to try this a different way. Thanks for responding though.

Gurmanjot Singh · Accepted Answer · 2017-06-06 06:29:04Z

1

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:

edited Jun 6, 2017 at 6:29

answered Jun 6, 2017 at 5:00

Gurmanjot Singh

10.4k2 gold badges22 silver badges46 bronze badges

Comments

Incorporeal Logic · Accepted Answer · 2017-06-05 19:17:53Z

-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 5, 2017 at 19:17

Incorporeal Logic

2803 silver badges18 bronze badges

1 Comment

waterwalk77 Over a year ago

Another interesting idea. Not sure how to code this yet but will look at this too. Thx.

Collectives™ on Stack Overflow

VBScript Replace for character string of unknown length

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related