Delete consecutive spaces

[Guus2005]

Looking for a way to delete consecutive spaces.
Preferably a one-liner

The replace command doesn't cut it.

?replace("Dim intI              as Integer","  "," ")

results in

Dim intI       as Integer

I was thinking of a regular expression but i need some help there.

Thanks.

 

[Minty]

Add a space to the search string and remove the space from the replace - nearly gets you there;

?replace("Dim intI              as Integer","   ","")
Dim intI  as Integer

 

[theDBguy]

Hmm, a regular expression won't probably be a one-liner unless you put it in a function and call it in your one line code. Just thinking out loud...

 

[Guus2005]

Problem is i don't know how many spaces are in the string. I don't want to loop the replace command until the length doesn't change anymore.

And i found that \s{2,} as a regular expression selects all consecutive spaces (2 or more).
Next step would be to replace it with a single space...

Keeping you posted.

 

[arnelgp]

Public Function RegExpReplace(ByVal WhichString As String, _
        ByVal Pattern As String, _
        ByVal ReplaceWith As String, _
        Optional ByVal IsGlobal As Boolean = True, _
        Optional ByVal IsCaseSensitive As Boolean = True) As String
    With CreateObject("vbscript.regexp")
        .Global = IsGlobal
        .Pattern = Pattern
        .IgnoreCase = Not IsCaseSensitive
        RegExpReplace = .Replace(WhichString, ReplaceWith)
    End With
End Function

expr = RegExpReplace(expr, "[\ ]{2,}", " ")

 

[Guus2005]

Thanks arnelgp. I will use your code.

 

[sxschech]

Sorry. reread post and mentions not to loop. Leaving in case of use to someone else, or let me know and I'll delete.

Non regex vba:

Function RemoveEXTRASpaces(SpaceyWord As String) As String
'http://www.vbforums.com/showthread.php?609658-REmoving-multiple-spaces-from-a-string
'Remove extra spaces
'20151112
    Do While InStr(1, SpaceyWord, "  ")
        SpaceyWord = Replace(SpaceyWord, "  ", " ")
    Loop
    
    'Do While True
    '    Mlen1 = Len(SpaceyWord)
    '    SpaceyWord = Replace(SpaceyWord, " ", " ") '2 spaces replaced with 1
    '    Mlen2 = Len(SpaceyWord)
    '    If Mlen2 = Mlen1 Then
    '        Exit Do
    '    End If
    'Loop
    RemoveEXTRASpaces = SpaceyWord
End Function

? removeextraspaces("Dim intI              as Integer")

Dim intI as Integer