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:

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.

3 Responses to “Macro: Keep only ASCII characters 32-127”

  1. 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

  2. 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

  3. 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

Pinterest