CODEWITHSUNDEEP
excelvbaspecial-characters
isomericharsh
isomericharsh

Reputation: 83

how to remove special characters from all the cells in sheet

I am currently using a function "Cleanstring" to remove any special characters in a particular column in a sheet. I use a for loop to run through all the cells in the column and call the function. The code runs fine, the problem is it takes a lot of time. Is there a faster way of getting the job done.

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

Upvotes: 2

Views: 615

Answers (1)

Tom
Tom

Reputation: 9878

This is a very fast way to do it using Regular Expressions and a VBA Array. It uses early binding so you will need to add a VBA reference to "Microsoft VBScript Regular Expressions 5.5"

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

This could also be written as a Function

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

And called as

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

Upvotes: 4

Related Questions