Macro: Keep only ASCII characters 32-127
Last week I received the following question:
I get sent Excel files from various vendors. I have to convert these to SAS datasets that get moved to a UNIX system. The SAS datasets can only contain ASCII 32-127. So I need to remove these from the Excel file.
You can use the "Text » Advanced character remove or replace..." utility to remove these characters. However it can be a bit time consuming to select these specific characters in the utility (although it will remember your settings so the next time will be quicker).
If you need to do this often then a custom macro might be easier to use.
You can use the following macro to remove every character except for ASCII character 32-127:
' Remove all ASCII characters that are not in the range of 32 till 127
' from the selected cells
Dim i As Long
Dim j As Long
Dim rngCel As Range
Dim sValue As String
Dim lTotal As Long
On Error GoTo ErrHandler
Application.EnableCancelKey = xlErrorHandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lTotal = Selection.Cells.Count
For Each rngCel In Selection
Application.StatusBar = "Removing ASCI characters <32 and > 127 in " & _
lTotal & " cells... " & Format(j / lTotal, "0%")
sValue = rngCel.Value
If Len(sValue) > 0 Then
For i = 1 To 255
If i < 32 Or i > 127 Then
sValue = Replace(sValue, Chr(i), "")
End If
Next i
End If
rngCel.Value = sValue
j = j + 1
Next rngCel
Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub
ErrHandler:
MsgBox "Sorry, something unexpected when wrong." & vbNewLine & _
"Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub
Note: the macro loops through each cell. A general find/replace might be faster but gives problems with ASCII characters 1 and 2 because with those characters everything gets removed.
Filed under: General, How to, how to with ASAP Utilities on August 13th, 2009 by Bastien | 3 Comments »
Tweet
Hi Bastien,
May the couple Live 1000 years !
Seems I am missing something. Where I can find the utility which selects all the cells having cell format similar to current cells formatting.
Regards
CA Kanwaljit Singh Dhunna
Hello Kanwaljit,
In the Excel menu choose ASAP Utilities » Select » Select cells based on content, formatting and more…
http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=7&utilities=2&lang=en_us
Kind regards,
Bastien
hi from australia. this macro is great, thank you for posting it. it will help us do some data cleansing. i like the way you used the for loop to do the whole selection, very clever. regards jason