excelvba

Remove all special characters except space


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.


Solution

  • 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.)