代码之家 › 专栏 › 技术社区 › Tiago

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

vba excel

2

Tiago · 技术社区 · 7 年前

我正在尝试从一个范围中删除所有特殊字符。我让它只维护数字和字母,但它也删除了空格。因为字符串有多个单词,所以应该保留空格。它应该保持字母数字+空格。

我到现在为止都有:

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

似乎我找不到要维护的空间代码。

2 回复 | 直到 7 年前

1

-1

pnuts 7 年前

如果不需要点,那么不要将它们包含在允许的字符类中,例如

If xTemp Like "[a-z]"

而不是

If xTemp Like "[a-z.]"

( "[0-9]" 不会保留数字中的小数点。)

2

1

VBasic2008 7 年前

删除非字母数字,包括空格

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