how to remove special characters from all the cells in sheet

Question

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

Possible duplicate of [Removing special characters VBA Excel](https://stackoverflow.com/questions/24356993/removing-special-characters-vba-excel) — Pankaj Jaju, May 07 '19 at 10:30
If you have a finite, reasonable number of special characters, and a large number of cells, you could use the `Range.Replace` method to process the entire column at a time. Otherwise, consider `Regular Expressions` and go cell by cell, but read it into a VBA array first. — Ron Rosenfeld, May 07 '19 at 10:31
@RonRosenfeld How to tackle that "VBA array first". Any code sample plz — isomericharsh, May 07 '19 at 10:36
If you want to go that route, see the late Chip Pearson's website article [Arrays and Ranges in VBA](http://www.cpearson.com/Excel/ArraysAndRanges.aspx). Read your range into the array. Use `Regular Expressions` to process each item in the array. Then write it back to the worksheet. There are also SO articles on using Regular Expressions in VBA — Ron Rosenfeld, May 07 '19 at 10:40

Tom · Answer 1 · 2019-05-07T10:54:04.920

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

how to remove special characters from all the cells in sheet

1 Answers1