CODEWITHSUNDEEP
vbaexcel
Jebathon
Jebathon

Reputation: 4561

Excel VBA - Unable to remove extra spaces between words using Trim and similar functions

I am trying to remove unnecessary white space in a string using VBA but the trim funciton doesn't seem to work for me. I want to remove the extra space between 'End' and 'Time' in this hypothetical example. 

Public Sub RemoveSpaces()

    Dim s As String: s = "START TIME: N/A END     TIME: N/A "

    s = Trim(s)

    Debug.Print s

    End Sub

enter image description here

Upvotes: 0

Views: 4101

Answers (5)

Alex
Alex

Reputation: 97

do until instr(s, "  ") = 0
s = replace(s, "  ", " ")
loop

Or if you want to do a UDF you can do something like this, then to remove double spaces you can just do s = RmvDblSpc(s)

Function RmvDblSpc(What As String) As String

Do Until InStr(What, "  ") = 0

    What = Replace(What, "  ", " ")

Loop

RmvDblSpc = What

End Function

Upvotes: -1

Comintern
Comintern

Reputation: 22195

Simplest (and probably the best performing) way of doing this is with a regular expression:

'Requires reference to Microsoft VBScript Regular expressions
Private Function RemoveExtraSpace(inVal As String) As String
    With New RegExp
        .Pattern = "\s+"
        .Global = True
        RemoveExtraSpace = .Replace(inVal, " ")
    End With
End Function

Sub Example()
    Dim s As String
    s = "START TIME: N/A END     TIME: N/A "

    Debug.Print RemoveExtraSpace(s)
End Sub

Late bound version:

Private Function RemoveExtraSpace(inVal As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\s+"
        .Global = True
        RemoveExtraSpace = .Replace(inVal, " ")
    End With
End Function

EDIT: If per comments you need multiline, just added the additional parameter:

With New RegExp
    .Pattern = "\s+"
    .Global = True
    .MultiLine = True
    RemoveExtraSpace = .Replace(inVal, " ")
End With

If you want the line breaks to remain, change the pattern to .Pattern = "[ ]+" (brackets for readability).

Upvotes: 4

Matt Cremeens
Matt Cremeens

Reputation: 5151

Off the top of my head, I'd use the split function and then join the pieces back together.

Dim s As String: s = "START TIME: N/A END     TIME: N/A "
sParts = Split(s, " ")
s_wo_xtra_space = ""
for i = lbound(sParts) to ubound(sParts)
    if sParts(i) <> "" then
        s_wo_xtra_space = s_wo_xtra_space & sParts(i) & " "
    end if
next i
s_wo_xtra_space = trim(s_wo_xtra_space)

This assumes you still want one space between words.

Upvotes: 1

hstdggsdtgsdafssarf456
hstdggsdtgsdafssarf456

Reputation: 129

Maybe app.Worksheetfunction.Trim?

Public Sub RemoveSpaces()

    Dim s As String: s = "START TIME: N/A END     TIME: N/A "

    s = Application.WorksheetFunction.Trim(s)

    Debug.Print s

End Sub

Oh, I see @Alexis Olson was first with this.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

You can use split() to split on the spaces then loop through and skip all the empty items in the resultant array:

Public Sub RemoveSpaces()

Dim s As String: s = "START TIME: N/A END     TIME: N/A "
Dim str() As String
Dim str1

str = Split(s)
s = ""
For Each str1 In str
    If str1 <> "" Then
        s = s & str1 & " "
    End If
Next str1

s = Trim(s)


Debug.Print s

End Sub

Upvotes: 1

Related Questions