There are times when you get text that has unprintable characters in them or extra spaces, typically from downloaded data or perhaps extracted data. This routine cleans your selected cells of thes characters.
-Selva V Pasupathy, HSBC Global Resources, Hyderabad.
Original Source: Ivan F Moala’s Site http://www.xcelfiles.com/VBA_Quick10.html
‘// From Help Files:
‘// CLEAN > Removes all nonprintable characters from text.
‘// Use CLEAN on text imported from other applications that
‘// contains characters that may not print with your
‘// operating system.
‘// For example, you can use CLEAN to remove some low-level
‘// computer code that is frequently at the beginning and end
‘// of data files and cannot be printed.
‘// TRIM > Removes all spaces from text except for single
‘// spaces between words. Use TRIM on text that you have
‘// received from another application that may
‘// have irregular spacing.
Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction
Set Func = Application.WorksheetFunction
On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox “No data to clean and Trim!”: Exit Sub
For Each oCell In CleanTrimRg
oCell = Func.Clean(Func.Trim(oCell))