Remove Unprintable Character & Spaces from cells

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
Option Explicit

Sub Clean_Trim()
‘// 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))
Next

End Sub

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: