VBA code to Remove Special charectors

Question

I need to remove prefixed special character, numbers in a string in a particular column, For example I am having data in column A (say from A1 to A10000) in column B i need result. below is my example strings

EX1: 12#- First code 12 /05 or 0000000- First code <12> take a trip

Result : First code 12/05 First code take a trip**

Like wise the same resulted data will be copied and all numbers should be replaced with "*" but the the special characters should not be removed.

Result in Column c should be as mentioned below.

     First code **/**
     First code <$**.00> take a trip**

In your example, how does `0000000- First code <12> take a trip` becomes `First code <$**.00> take a trip**` ? Why `$**.00`? — J. Chomel, Mar 16 '16 at 08:55
For us to help, you must at least show us you tried something, and the errors you got. — J. Chomel, Mar 16 '16 at 08:55
I have tried the code , and it has removed all special characters and numbers — Naveen Sagar, Mar 16 '16 at 12:04
Dim RE As Object Dim rng As Range If Range("A2") <> "" Then Range("A2").Select Set RE = CreateObject("VBScript.RegExp") With RE .Global = True .Pattern = "[^a-zA-Z><= ]" For Each rng In Sheets("All_Errors").Range("B2:B40000").SpecialCells(xlCellTypeConstants) rng.Value = .Replace(rng.Value, "") rng.Value = Trim(rng.Value) Next rng End With — Naveen Sagar, Mar 16 '16 at 12:17

score 0 · Answer 1 · edited May 23 '17 at 12:31

This is good for me to work a little on regexp, so here is how you could do what you want --- I write the first "code-and-special-chars" part in cell B, use cell C as a temporary so that it does not look too magical, and result will be in column D:

Public Sub that()
    mySheet = "regexp"
    Dim strInput As String
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")

    Set Myrange = ThisWorkbook.Sheets(mySheet).Range("A1:A1000")
'    re.Pattern = ThisWorkbook.Sheets(mySheet).Cells(1, 8).Value
    re.Pattern = "([^a-zA-Z \r\t\n\f]*)(.*)(.*)"
    For Each C In Myrange
        If re.Pattern <> "" Then
            strInput = C.Value    
            If re.test(strInput) Then
                C.Offset(0, 1) = re.Replace(strInput, "$1")
                C.Offset(0, 2) = re.Replace(strInput, "$2")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If

        End If
    Next
' then do it again to replace numbers
    Set Myrange = ThisWorkbook.Sheets(mySheet).Range("C1:C1000")
    re.Pattern = "[0-9]"       
    For Each C In Myrange
        strInput = C.Value
        Do While re.test(strInput)
            strInput = re.Replace(strInput, "*")
            ' MsgBox (strInput)
        Loop
        C.Offset(0, 1) = strInput
    Next
End Sub

This answer was totally useful to me.

Hi Thanks a lot this coding is working fine for me, but i need to make little changes in this, I will make those changes to my requirement and If I need more help, I will contact you. — Naveen Sagar, Mar 16 '16 at 12:56
I ran the same code and altered little for my requirement but i am facing a problem in this. for example, I am having a string which starts with "1st car is red" here i need the output as "1st car is red" but i am getting output as "st car is red". — Naveen Sagar, Mar 16 '16 at 18:52
Hi, this is what I understood from your requirements: the leading numbers had to be removed: _I need to remove prefixed special character, numbers in a string_; and this is what your example shows when `12#- First code 12 /05` becomes `First code **/**` . Now if you want something a little more specific, please **accept my first answer** and comment your question about possible special cases. — J. Chomel, Mar 17 '16 at 07:01

VBA code to Remove Special charectors

1 Answers1