VBA - Remove Leading and Trailing Spaces from a Column in Excel

[paulfitz320]

Hi

Could anybody post a VBA routine to remove LEADING and TRAILING spaces from a Column of Text in Excel please. COL A will do for example purposes. Cant find exactly what I'm looking for on Google.

Thanks

 

[Fluff]

Re: Simple VBA - Remove Leading and Trailing Spaces from a Column in Excel

If they are normal spaces try

Sub paulfitz320()
   Dim Cl As Range
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Value = Trim(Cl.Value)
   Next Cl
End Sub

 

[Jaafar Tribak]

Re: Simple VBA - Remove Leading and Trailing Spaces from a Column in Excel

Or

 Columns(1).Value = Evaluate("if(Row(), Trim(" & Columns(1).Address & "))")

 

[Fluff]

Re: Simple VBA - Remove Leading and Trailing Spaces from a Column in Excel

Jaafar
That will also remove any extra spaces within the cell, not just leading/trailing spaces.

 

[Jaafar Tribak]

Re: Simple VBA - Remove Leading and Trailing Spaces from a Column in Excel

Jaafar
That will also remove any extra spaces within the cell, not just leading/trailing spaces.

True.... I missed that.

Thank you.

 

[paulfitz320]

Re: Simple VBA - Remove Leading and Trailing Spaces from a Column in Excel

Thanks very much for the replies.

I have one more....

A VBA routine to replace all values in COL A that are greater than 15 and less than 999, with the number 16.

 

[Fluff]

Re: Simple VBA - Remove Leading and Trailing Spaces from a Column in Excel

As that is a different question, please start a new thread.
Thanks

 

[tbrock]

I am using the following code to remove leading and trailing "white space" from cell comments. It will remove characters outside of the range listed in the select case statement. So all line breaks, carriage returns, non-breaking spaces, etc. will be removed from the beginning or the end of whatever text is input.

Comments:

• This seems to work perfectly, but as I am not an expert it would not surprise me that there is a faster way to do this or that someone will find a way to break it.
• While this is for cell comments, it should be easily adaptable to the task at hand here.
• I decided to include all printable characters from Chr(33) thru Chr(126) as triggers for start and end position. This is easy enough to limit to a subset as needed.
• Also, this is limited/hardwired to a comment in Range("A1") for demonstration purposes.
• This, again, does not trim/clean anything EXCEPT leading and trailing white space. It would be easy enough to combine it with Trim$(text) to also remove extra spaces within the text as I will do in my application. I personally do not want to remove and linebreaks/carriage returns within the actual kept text since my comments are formatted into paragraphs. So I will use Trim (and not Application.Trim). Your mileage may vary.
  
  

  Sub TrimComments()
  [/LIST]
      Dim i As Integer, j As Integer, start As Integer, finish As Integer
      Dim curComment As String
      Dim workstring As String
  
      curComment = Worksheets("Sheet1").Range("A1").Comment.Text
      workstring = UCase$(curComment)
      
      j = Len(workstring)
      
      ' Get position of first printable character
      For i = 1 To j
          Select Case Asc(Mid(workstring, i, 1))
              Case 33 To 126: start = i: Exit For
          End Select
      Next i
      
      ' Get position of last printable character
      For i = j To 1 Step -1
          Select Case Asc(Mid(workstring, i, 1))
              Case 33 To 126: finish = i: Exit For
          End Select
      Next i
  
      ' Trim Comment and reassign back to cell comment.  Note that the obvious Comment.Text = seems to fall over. 
      ' This assignment *does* work (Excel 2013) in reassigning the edited comment back into the cell comment.
      Worksheets("Sheet1").Range("A1").Comment.Text Text:=Mid$(curComment, start, (finish - start + 1))
      
  End Sub