excel - 删除除空格外的所有特殊字符

Question

我正在尝试从一个范围内删除所有特殊字符。我让它只保留数字和字母，但它也删除了空格。由于字符串有多个单词，因此应该保留空格。它应该保持字母数字+空格。

到目前为止我所拥有的:

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

看来我无法弄清楚要维护的空间代码。

Answer 1

删除非字母数字，包括。空间

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