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:
Sub sbRemoveASCII_Outside32_127_B()
' 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.