Get rid of part of a string when the part to delete is at an unknown position

[leighhobson89]

I'm having a blank moment.

Once a string variable has been set, I basically need to check it for certain substrings within it, and get rid of them, and join the string back up. It will mainly be getting rid of any occurrences where there is a double, concurrent occurrence of "%2B" in the string, i.e. "%2B%2B"

Can someone just tell me how to pull that out, replace it with "%2B" i.e. one occurrence, and leave the rest of the string as it is.

Dead simple I'm sure, but it has to be in VBA to slot into my code.

I am basically along these lines, but clearly its wrong or I wouldn't be writing on here:

searchString = Replace(searchString, " ", "%2B")
    
    posOfDoubleSpace = InStr(1, searchString, "%2B%2B")
    
    Do While posOfDoubleSpace <> 0
        posOfDoubleSpace = InStr(1, searchString, "%2B%2B")
        right = searchString
        searchString = Left$(searchString, posOfDoubleSpace - 1) + "%2B"
        lengthSearchStringLeft = Len(searchString)
        right = right$(right, Len(right) - lengthSearchStringLeft)
    Loop

Thanks for your help in advance

 

[Gerald Higgins]

I'm not a macro expert, but I tried recording a macro while I did this job manually, and this is the code I got.

Selection.Replace What:="%2B%2B", Replacement:="%2B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

It seems to work, maybe you can incorporate some of this into what you are doing ?

 

[Tetra201]

Will this work for you?

searchString = Replace(Replace(searchString, " ", "%2B"), "%2B%2B", "%2B")

 

[leighhobson89]

Thanks guys, Tetra201's answer was the one I went with. Easy when you know how huh!

 

[Tetra201]

Glad to be of a help.

The following might also work, but it will remove the leading/trailing spaces:

searchString = Replace(WorksheetFunction.Trim(searchString), " ", "%2B")

 

[leighhobson89]

Again thanks very much, infact I had already implemented a seperate code to do that for the end (the front would never happen in my case.) As before, less efficient than yours, but it worked, thus:

Do While right$(searchString, 3) = "%2B"
        searchString = Left$(searchString, Len(searchString) - 3)
Loop