Remove all special characters and spaces

Alt+F11 - Insert - Module - Paste it in.

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "/:*?""<>|"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function


You can add more special characters into the array if you wish.

Then in your cell, type: =SUBSTITUTE(removeSpecial(A1)," ","")