Find & Remove Special Character

[sadasivan]

Hi All
Please find the attachment , how to find & remove special character between alpha numeric in a cell

 

[Nebu]

Hi:

May be this code:

Sub test()
Application.ScreenUpdating = False
Dim rng As Range
Dim strg$

i& = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For Each rng In Sheet1.Range("A2:A" & i).Cells
    For j& = 1 To Len(rng)
        Select Case Asc(Mid(rng, j, 1))
            Case 48 To 57, 65 To 90, 97 To 122:
                strg = strg & Mid(rng, j, 1)
                Sheet1.Cells(rng.Row, rng.Column + 1) = strg
        End Select
    Next
    strg = vbNullString
Next
Application.ScreenUpdating = True
End Sub

Thanks

 

[Nebu]

Hi:

Slightly faster code using regular expression.

Sub test1()
Application.ScreenUpdating = False
Dim obj1 As RegExp
Set obj1 = New RegExp

i& = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For Each rng In Sheet1.Range("A2:A" & i).Cells
    obj1.Pattern = "[^\w ]"
    obj1.Global = True
    Sheet1.Cells(rng.Row, rng.Column + 1) = obj1.Replace(rng, "")
Next
Application.ScreenUpdating = True

End Sub

Note: Add Microsoft VBScript Regular Expressions as a reference to your subroutine

Thanks

 

[jindon]

According to the special characters in Sheet3, should be

Sub test()
    Dim a, i As Long
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        With CreateObject("VBSCript.RegExp")
            .Global = True
            .Pattern = "[\W_ ]"
            For i = 2 To UBound(a, 1)
                a(i, 1) = .Replace(a(i, 1), "")
            Next
        End With
        .Value = a
    End With
End Sub