Removing Special characters in excel sheet.

[rgarg06]

Hi Guys, I am trying to remove all the special character in excel sheet at once. But replacing the special characters one by one is not helping though. Is there any other way to do that or through VBA?

 

[Michael M]

Maybe this way

Sub MM1()
    Const sBad As String = "`!@#$;^()_-=+{}[]\|;:'"",.<>/?"
    Dim cell As Range, s As String, i As Long
    Application.ScreenUpdating = False
    For Each cell In ActiveSheet.UsedRange
        If VarType(cell.Value) = vbString Then
            s = cell.Value
            For i = 1 To Len(sBad)
                s = Replace(s, Mid(sBad, i, 1), "")
            Next i
            cell.Value = Application.Trim(s)
        End If
    Next cell
    Application.ScreenUpdating = True
End Sub

 

[Legacy 456155]

Not sure what your special chars are. This will remove non printable characters.

Sub CleanCells()
    Dim r As Range
    For Each r In UsedRange
        r = WorksheetFunction.Clean(r)
    Next
End Sub