Trimming Spaces

[awappel]

I am having trouble trimming spaces in excel. I have searched the forum at attempted several macro's including the one below:

Sub TrimSpaces()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
cell = WorksheetFunction.Trim(cell)
Next cell
End Sub

But I still have the extra spaces. I have also just tested a few cells by using the trim () function, and I still get extra spaces after the words. I have done the trim function in the past and not had a problem. Is there something potentially wrong with my excel? or do I have my field in an incorrect format?

Thanks for any help you can give, I am a fairly inexperienced user.

 

[VoG]

Try selecting the range then running the TrimAll macro http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[jakeb3482]

have you tried the clean function?

clean(text) will remove any unprintable characters

 

[Andrew Poulsom]

Welcome to MrExcel.

Maybe you have some Chr(160) spaces. Try:

Sub TrimSpaces()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
        cell = WorksheetFunction.Trim(Replace(cell.Value, Chr(160), ""))
    Next cell
End Sub

 

[awappel]

Thank you for all your help. That last function worked perfectly. I will make sure to hold onto that!!!!!