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:
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
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), "")
rngCel.Value = sValue
j = j + 1
Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Sorry, something unexpected when wrong." & vbNewLine & _
"Error " & Err.Number & ": " & Err.Description, vbCritical
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.