Removing unprintable, unreadable character (Replace ASCII Characters from Excel WorkSheet)

Removing unwanted, unprintable characters in a Cell in Excel!!

We copy and paste data from web source into excel and use it for further detailing, analysis using excel. Data of teams, districts, states when copied from web, it could have some unprintable, non-viewable ascii characters within Cell. Due to such character presence excels functions such as vlookup, hlookup, search, find, countif fails to give accurate and reliable result. We can consider following approaches to solve our problem:-

1. Enable Macros 
2. Open VBE using Alt + F11
3. Insert Module 
4. Paste Below Code

---------------------------------------------------------------------------------

Option Explicit

Dim Currow, LastRow, LastCol As Integer

Dim Curcol As String

Sub Remove_NonPrintable_Characters()

Application.ScreenUpdating = False

 If Range("A1").Value <> "" Then

        LastCol = Range("IV1").End(xlToLeft).Column

        LastRow = Cells(Rows.Count, "A").End(xlUp).Row

        

        Range("A1").Select

     Do

        Curcol = Split(ActiveCell.Address, "$")(1)

        Do

            Currow = Selection.Cells.Row

            Range(Curcol & Currow).Value = removeall(Range(Curcol & Currow).Value)

            ActiveCell.Offset(1, 0).Select

        Loop Until Currow >= LastRow

            ActiveCell.Offset((0 - Currow), 1).Select

     Loop Until Selection.Cells.Column > LastCol

    End If

    

Application.ScreenUpdating = True

End Sub

Public Function removeall(CellValue As String) As String

Dim ErrChar As Integer

Dim NewVal As String

Dim i As Integer

For i = 1 To Len(CellValue)

    

    ErrChar = Asc(Mid(CellValue, i, 1))

    

    Select Case ErrChar

        Case Is < 91 And ErrChar > 64

            NewVal = NewVal & Chr(ErrChar)

        Case Is < 123 And ErrChar > 96

            NewVal = NewVal & Chr(ErrChar)

        Case Is = 32

            NewVal = NewVal & Chr(ErrChar)

        Case Is < 59 And ErrChar > 44

            NewVal = NewVal & Chr(ErrChar)

  

    End Select

Next i

removeall = NewVal

End Function

---------------------------------------------------------------------------------

(Click Here for ASCII Table)