Macro to remove trailing spaces in cells

[Victor Delta]

I use a large worksheet which, in part, is populated by cutting and pasting
data from word documents, emails etc. This means that many cells, which
contain text, actually also have some (invisible) trailing spaces. Whilst
this doesn't cause a huge problem, it does make using some Excel facilities,
such as pivot tables, difficult for obvious reasons.

Does anyone please know of any macro or other device that could remove these
trailing spaces whilst leaving the ones between words etc intact?

Thanks,

V

 

[Dave Peterson]

Is all your data in a single column? And is it ok to remove any leading spaces?

If yes to both, then you could try this:
Select the column of data
Data|text to columns (in xl2003 menus)
Choose Fixed width
Remove any lines that excel guessed and don't add any yourself.
Finish up the wizard.

If you really need a macro, you could record one when you do it manually.

ps. If your data is over several columns, I bet it would still be quicker to
use multiple data|text to columns than to loop through each of the constants in
the range.

But you could use something like this:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range that has constants!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = RTrim(myCell.Value)
Next myCell

End Sub

 

[Harald Staff]

Hi Victor

This works selected cells:

Sub RemoveLeadingTrailingSpaces()
Dim Rng As Range, Cel As Range
On Error Resume Next
Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
If Rng Is Nothing Then Exit Sub
Application.Calculation = xlManual
For Each Cel In Rng
If Cel.HasFormula = False And Cel.Value <> "" Then
If InStr(Cel.Value, Chr(160)) > 0 Then _
Cel.Value = Replace$(Cel.Value, Chr(160), Chr(32))
If Cel.Value <> Trim$(Cel.Value) Then _
Cel.Value = Trim$(Cel.Value)
End If
Next
Application.Calculation = xlAutomatic
End Sub

The Chr(160) is HTML Nonbreakingspace; hard to spot and get rid og in Excel
without code.

HTH. Best wishes Harald

 

[Victor Delta]

Hi Victor

This works selected cells:

Sub RemoveLeadingTrailingSpaces()
Dim Rng As Range, Cel As Range
On Error Resume Next
Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
If Rng Is Nothing Then Exit Sub
Application.Calculation = xlManual
For Each Cel In Rng
If Cel.HasFormula = False And Cel.Value <> "" Then
If InStr(Cel.Value, Chr(160)) > 0 Then _
Cel.Value = Replace$(Cel.Value, Chr(160), Chr(32))
If Cel.Value <> Trim$(Cel.Value) Then _
Cel.Value = Trim$(Cel.Value)
End If
Next
Application.Calculation = xlAutomatic
End Sub

The Chr(160) is HTML Nonbreakingspace; hard to spot and get rid og in
Excel without code.

HTH. Best wishes Harald

Many thanks to both of you for your replies and suggestions. I'll give them
all a go!

V