Remove all special characters except space

Question

I'm trying to remove all special characters from a range. I've got it to maintain only numbers and letters, but it also removes spaces. Since The strings have more than one word, it's supposed to retain spaces. It should maintain alphanumeric + space.

What I've got until now:

Sub RemoveNotAlphasNotNum()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[a-z.]" Or xTemp Like "[A-Z.]" Or xTemp Like "[0-9.]" Or xTemp Like "[\s]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

It seems I can't figure out the space code to maintain.

perhaps [vba regexp](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) will be more straightforward, or you can add `Or xTemp = " "` — Scott Holtzman, Jan 20 '19 at 15:41
it seems it works, but I noticed that my code doesn't remove dots also (.). Tried with "." too... But nothing. any suggestion? — Tiago, Jan 20 '19 at 16:05

score 1 · Answer 1 · answered Jan 20 '19 at 16:22

Remove Non Alpha Numerics incl. SPACE

Option Explicit

Sub RemoveNotAlphasNotNum()

    Dim Rng As Range
    Dim WorkRng As Range
    Dim xTitleId As String
    Dim xOut As String
    Dim xTemp As String
    Dim xStr As String
    Dim i As Long

    On Error Resume Next
    xTitleId = "Range"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

    For Each Rng In WorkRng
        xOut = ""
        For i = 1 To Len(Rng.Value)
            xTemp = Mid(Rng.Value, i, 1)
            If xTemp Like "[ 0-9A-Za-z]" Then
                xStr = xTemp
              Else
                xStr = ""
            End If
            xOut = xOut & xStr
        Next i
        Rng.Value = xOut
    Next

End Sub

score -1 · Accepted Answer · answered Jan 20 '19 at 16:14

-1

If you don't want dots then don't include them in the allowed character classes, eg

If xTemp Like "[a-z]"

rather than

If xTemp Like "[a-z.]"

( "[0-9]" will not preserve decimal points in numbers.)

answered Jan 20 '19 at 16:14

pnuts

58,317
11
87
139

Remove all special characters except space

2 Answers2

Remove Non Alpha Numerics incl. SPACE