replace variable amount of commas with one comma 1

[glgcag]

I have created a function that accepts two comma separated text strings sNew and sExisting- I put both csv strings into an array then I check sNew against sExisting to see if any of the sNew are contained in sExisting. If so, I remove the sNew value from sExisting.

The problem I'm running into is this:

If sNew = "2,56,734"

And sExisting = "1,2,56,734,5009"

the resulting string is "1,,,,5009"

I want to return "1,5009". How do I remove the extra commas, taking into account that I won't know how many there will be at any given point? Here's essentially what I want to do in plain language:

If I have > 1 comma next to each other, replace it with a single comma.

Any suggestions?

 

[genomon]

Replace(resultstring,",,",")
May have to do it a few times to get rid of multiples
(i.e. 1,,,,2 would become 1,,2 on the first iteration).
There is probably a more elegant solution using Instr() or Mid(), this just came to mind...

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[glgcag]

genomon,

Yeah, I was going that route and thought there might be something more elegant too- alas, here's what I came up with:

Private Sub cmdTest_Click()
    Dim bEqual As Boolean
    Dim sParsed As String
    Dim sNew As String
    
    sParsed = "1,,,,5009"
    Do Until bEqual = True
        sNew = Replace(sParsed, ",,", ",")
        If Len(sParsed) = Len(sNew) Then
            bEqual = True
        Else
            bEqual = False
            sParsed = sNew
        End If
    Loop
    MsgBox sParsed
End Sub

Feels like a lot of code to do something that one function may be able to handle, but I'll use it as is for now. Thanks!

 

[Remou]

How about:

[tt]Do While InStr(sParsed,",,")>0[/tt]

 

[glgcag]

Remou,

Much more succinct, thank you! Ended up with:

Do While InStr(sParsed,",,")>0
   sParsed = Replace(sParsed,",,",",")
Loop

Thanks for showing me the short way to write it! Sometimes I complicate it too much!

 

[genomon]

Remmy gets another star!!!

Two vultures board an airplane, each carrying two dead raccoons. The
stewardess looks at them and says, "I'm sorry, gentlemen, only one
carrion allowed per passenger."