Removing Double Blanks in String

[mutley]

Hi

How can I to remove double Blanks in String

Example:

  myvar= "tes ssss   trx    ssss     xxxx            ssssss"
 
I want

     myvar= "tes ssss trx ssss xxxx ssssss"

thanks

[shakti5385]

Use Replace Function

[bushmobile]

Do While InStr(YourString, "  ")
    YourString = Replace(YourString, "  ", " ")
Loop

[mutley]

only Replace did not work

replace("tes ssss   trx    ssss     xxxx            ssssss","  "," ")
 
tes ssss  trx  ssss   xxxx      ssssss

I do not want to use a LOOP

[tward]

Loop is the only way....

[mutley]

I find (in Brazil call : GAMBIARRA or RTA (Resource Techinical Alternative))

replace(replace(replace("tes ssss   trx   ssss","  "," _"),"_ ","_"),"_","")

[bushmobile]

and what happens if the string already has underscores in it...?

why don't you want to use a loop?

[mutley]

BOSS


IF string already has underscores I am lost

[gavio]

I really can't see why you don't want to use the loop since it's prodiving a perfect solution...

[tward]

And from What I can see, really the only solution....

[leinad31]

mutley, you don't know how many spaces there are... three calls to replace may not be enough. That's why your testing that in the Do loop with this line

Do While InStr(YourString, "  ")

Which translates as, "as long as there are two adjacent spaces in string, run the iteration." And the iteration consists of calling replace.

[CVMichael]

I just wrote this, it is faster than using Replace in a loop, and really fast when it comes to large strings:

Option Explicit
 
Private Sub Form_Load()
    Debug.Print RemoveChar("tes ssss   trx    ssss     xxxx            ssssss")
End Sub
 
Private Function RemoveChar(Str As String, Optional Char As String = " ") As String
    Dim Buff() As Byte, K As Long, I As Long
    Dim CharByte As Byte
    
    CharByte = Asc(Left(Char, 1))
    Buff = StrConv(Str, vbFromUnicode)
    
    Do
        If Buff(K) = CharByte Then
            Do While Buff(K) = CharByte
                K = K + 1
                If K >= UBound(Buff) Then Exit Do
            Loop
            K = K - 1
            If K >= UBound(Buff) Then Exit Do
        End If
        
        If K <> I Then Buff(I) = Buff(K)
        I = I + 1
        K = K + 1
    Loop Until K > UBound(Buff) Or I > UBound(Buff)
 
    ReDim Preserve Buff(I - 1)
    
    RemoveChar = StrConv(Buff, vbUnicode)
End Function

[bushmobile]

CharByte = Asc(Left(Char, 1))

can be simply done as

CharByte  = Asc(Char)