Trim Spaces(all)

**JapanDave** · 02-06-2012, 10:02 PM

I have been trying to write some code that will trim all spaces in between words of different length with no success, I can trim the spaces before and after, but no the spaces between words. Any one know some code to do this?

**nilem** · 02-06-2012, 10:39 PM

Maybe something like this (I took an example from another thread)

Sub ert()
With Range("E1:E" & Cells(Rows.Count, 5).End(xlUp).Row)
     .Offset(, 3).Value = Application.Trim(.Value)
End With
End Sub

**protonLeah** · 02-06-2012, 10:42 PM

Option Explicit

Sub MiddleSpaces()
    Dim Fragments   As Variant, _
        Ctrl        As Long, _
        NewString   As String
    
    Fragments = Split(Range("A1").Value, " ")
    
    For Ctrl = LBound(Fragments) To UBound(Fragments)
        NewString = NewString & IIf(Fragments(Ctrl) <> "", Fragments(Ctrl) & " ", "")
    Next Ctrl
    
    NewString = Trim(NewString)

End Sub

**rylo** · 02-06-2012, 11:13 PM

Hi

Another version

Sub aaa()
  Dim regex As Object
  Set regex = CreateObject("vbscript.regexp")
  
  With regex
    .Pattern = "\s{2,}"
    .Global = True
  End With
  
  For Each ce In Range("A2:A4")
    ce.Value = Trim(regex.Replace(ce, " "))
  Next ce
  
End Sub

rylo

**nilem** · 02-06-2012, 11:32 PM

or

Sub rty()
Dim i As Byte
For i = 1 To 3 ' 3 or more
    Range("E1:E" & Cells(Rows.Count, 5).End(xlUp).Row).Replace "  ", " "
Next i
End Sub

**Winon** · 02-07-2012, 02:44 AM

@ nilem,

Thank you nilem, I shall use your Code as per Post#2

**JapanDave** · 02-09-2012, 09:05 PM

Thanks guys, I have now got it sorted!

Thread: Trim Spaces(all)

Thread Tools

Display

Trim Spaces(all)

Re: Trim Spaces(all)

Re: Trim Spaces(all)

Re: Trim Spaces(all)

Re: Trim Spaces(all)

Re: Trim Spaces(all)

Re: Trim Spaces(all)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions