Trimming Spaces from Strings

This is a quick cleaner I use when I've imported data from another application. This removes all the extra spaces and hidden/non-printable Characters.
Sub Data_Cleaner()
'
' Macro to clean and trim data elements in a spreadsheet
' works on any size spreadsheet
'
Dim r As Long 'Declare a variable for counting rows
Dim C As Long 'Storage variable for counting columns
Dim SheetName ' Storage variable for open sheet name
Dim Columnl As Long
Dim Rowl As Long
Dim Bad As Variant ' unseen character that messes up other files when exporting

If (vbYes <> (MsgBox("Are you sure you want to Clean and Trim all cells in the worksheet?", _
vbYesNo, "Data Cleaner"))) Then
Exit Sub
End If
Application.Calculation = xlManual ' this stops calculations in sheets from running
Application.ScreenUpdating = False
SheetName = ActiveSheet.Name 'stores the name of the open worksheet
Bad = Chr(160)
Rowl = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'finds last row with a real character in it
Columnl = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'finds last column with a real character in it
For r = 1 To Rowl
For C = 1 To Columnl
Cells(r, C).Value = Application.WorksheetFunction.Substitute(Cells(r, C).Value, Bad, "")
Cells(r, C).Value = Application.WorksheetFunction.Clean(Cells(r, C).Value)
Cells(r, C).Value = Application.WorksheetFunction.Trim(Cells(r, C).Value)
On Error Resume Next
If Len(Cells(r, C).Value) = 0 Then
Cells(r, C).ClearFormats
On Error Resume Next
End If
Next C
Next r
Application.Calculation = xlAutomatic ' this sets calculations back to automatic
Application.ScreenUpdating = True
MsgBox "Finished!", vbOKOnly, "Data Cleaner"
End Sub

As it looks like the extra spaces got removed in my previous comment, I'm now substituting a "-" for a space, just to get by the auto-formatting.
So, if you also need to remove extra spaces from inside the text, use the worksheet function "Trim". E.g., Application.Trim("----Big---text---") will return "Big-text". Remember, change the "-" to a space.

In addition, if you also need to remove extra spaces from inside the text, use the worksheet function "Trim". E.g., Application.Trim(" Big text ") will return "Big text".