How to use vba to remove extra spaces between words

Hi all,

I am trying to remove extra spaces between words that are being inputed by a userform. I have tried uisng "= Trim(TextBox1.Text)" which works nicely for leading spaces and end spaces but is not effective for extra spaces between words. Anybody have an idea of what vba code is needed to remove the extra spaces between words??

Thanks!

possibly not the most efficient but one method:

Sub macro_1()
Dim str, str1, i
str = " asdf   asdfo asdfon as    asdfon  as"
str = Split(str)
For i = 0 To UBound(str)
    If Not str(i) = "" Then str1 = str1 & " " & str(i)
Next
str = Trim(str1)
End Sub

Is there any way to apply this code to an entire section on a worksheet??

Note: the worksheet function version of trim does remove spaces between words, so you could also do something like:

Sub macro_2()
Dim ws1, ws2
Set ws1 = ActiveSheet
Set ws2 = Sheets.Add
ws2.Range("A1:Z100").FormulaR1C1 = "=trim(" & ws1.Name & "!rc)"
ws2.Range("A1:Z100").Copy
ws1.Range("A1").PasteSpecial xlValues
Application.DisplayAlerts = False
ws2.Delete
Application.DisplayAlerts = True
End Sub

which would probably run faster for big ranges.

Sub macro_1()
Dim rng, str, str1, i, cl
Set rng = Range("A1:Z100") 'change to desired range
For Each cl In Range("A1:Z100").Cells
    If Not cl.Text = "" Then
        str = cl
        str = Split(str)
        For i = 0 To UBound(str)
            If Not str(i) = "" Then str1 = str1 & " " & str(i)
        Next
        cl = Trim(str1)
    End If
Next
End Sub

This will remove upto 4 extra spaces in Column A:

Sub JoeWalsh()
With Range("A2:A" & ActiveSheet.UsedRange.Rows.count)
    .Replace " ", " ", xlPart
    .Replace "  ", " ", xlPart
    .Replace "   ", " ", xlPart
    .Replace "    ", " ", xlPart
End With
End Sub

as an option

Sub tt()
Dim i&
For i = 4 To 2 Step -1
    Range("A1:A10").Replace Space(i), " "
Next
End Sub

Thanks for all the Help!

@abousetta, I think in VBA it only removes leading and following spaces...

I think to utilise the worksheet function you would need to do something like in post 6?

@ yudlugar

abousetta is correct.

VBa Trim() only removes leading and trailing spaces
The Excel function TRIM() removes all extra spaces

In VBa call the Excel function like so

Worksheetfunction.Trim(Range("A2")

or

Application.Trim(Range("A2"))

Thanks Marcol, I assumed the worksheetfunction wouldn't be available as there was a vba version.

I have a macro with this in it as i get a lot of exported data with eccess spaces.
basically it will turn all double spaces into single spaces and loop untill no double spaces remain

'clean up descriptions
    Do While True
        Set Description = Cells.Find(What:="  ", After:=Range("A1"), LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        If Description Is Nothing Then Exit Do
        Range("Description").Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Loop

Originally Posted by Marcol

@ Leon V (AW

Why?
When Worksheetfunction.Trim() does the job without looping, see post #9

Your code will not work properly if there are multiple leading or trailing spaces

I use vba trim for leading and trailing. Thought Excels trim doesn't work on some Unix spaces like ASCII 160