excel - 如何从工作表中的所有单元格中删除特殊字符

Question

我目前正在使用函数“Cleanstring”来删除工作表中特定列中的任何特殊字符。我使用 for 循环遍历列中的所有单元格并调用该函数。代码运行良好，问题是需要很多时间。是否有更快地完成工作的方法。

Function cleanString(str As String) As String
    Dim ch, bytes() As Byte: bytes = str
    For Each ch In bytes
        If Chr(ch) Like "[A-Za-z]" Then cleanString = cleanString & Chr(ch)
    Next ch
End Function

 For I = 1 To lrow
       Range("C" & I).Value = cleanString(Range("E" & I).Value)
    Next

Answer 1

这是使用正则表达式和VBA数组实现此目的的一种非常快速的方法。它使用早期绑定(bind)，因此您需要添加对“Microsoft VBScript Regular Expressions 5.5”的 VBA 引用

Sub DemoRegExr()
    Dim RegEx As New RegExp
    Dim arr As Variant
    Dim i As Long

    With ActiveSheet
        ' Range "E1:E3"
        arr = .Range(.Cells(1, 5), .Cells(3, 5)).Value2

        With RegEx
            .IgnoreCase = True
            .Global = True
            .Pattern = "[^A-Z ]"

            For i = LBound(arr) To UBound(arr)
                If .test(arr(i, 1)) Then
                    ' Using WorksheetFunction.Trim to remove double spacing
                    arr(i, 1) = WorksheetFunction.Trim(.Replace(arr(i, 1), vbNullString))
                End If
            Next i
        End With
        ' Range "C1:C3"
        .Cells(1, 3).Resize(UBound(arr)).Value2 = arr
    End With
End Sub

这也可以写成一个函数

Function cleanString(str As Variant) As String
    Dim RegEx As New RegExp

    ' Default value
    cleanString = str

    With RegEx
        .IgnoreCase = True
        .Global = True
        .Pattern = "[^A-Z ]"
        If .test(str) Then
            cleanString = WorksheetFunction.Trim(.Replace(str, vbNullString))
        End If
    End With
End Function

并称为

Sub DemoArr()
    Dim arr As Variant
    Dim i As Long

    With ActiveSheet
        ' Range "A1:A3"
        arr = .Range(.Cells(1, 5), .Cells(3, 5)).Value2

        For i = LBound(arr) To UBound(arr)
            ' Using WorksheetFunction.Trim to remove double spacing
            arr(i, 1) = cleanString(arr(i, 1))
        Next i

        .Cells(1, 3).Resize(UBound(arr)).Value2 = arr
    End With
End Sub