Replace all special characters with space

Question

I have a string of data... A lot of the data is alphanumeric and many have special characters (!@#$%^&*()_+=-{}[]:;"'<>?,./~` ) 

Example: 6-ct of Apple-skins (#green)

I need to replace all special characters with spaces. 

6 ct of Apple skins  green

Prefer vba (16k+ rows of data) but formula would work as well.

Answer 1

You can run the following macro (select the range first):

Sub ReplaceSpecial()

    Dim cel As Range

    Dim strVal As String

    Dim i As Long

    Application.ScreenUpdating = False

    For Each cel In Selection

        strVal = cel.Value

        For i = 1 To Len(strVal)

            Select Case Asc(Mid(strVal, i, 1))

                Case 32, 48 To 57, 65 To 90, 97 To 122

                    ' Leave ordinary characters alone

                Case Else

                    Mid(strVal, i, 1) = " "

            End Select

        Next i

        cel.Value = strVal

    Next cel

    Application.ScreenUpdating = True

End Sub

Answer 2

Thanx!

When I make my first million...

I'm buying you a Krystal... WITH cheese!