Want to remove Special Charaters From Address List

[Ajayjati25]

Dear Sir

I have address list of about 10k Dealers which contains various special Charactors Like !@#-&*-;,./ Now i want to remove this special Charactors from address list. Please help me out on this problem.

 

[Belleke]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    Application.EnableEvents = False
    Target.Value = ValidFilename(Target.Value)
    Application.EnableEvents = True
End Sub
Function ValidFilename(p_file As String) As String
    Dim nChars As String
    Dim i As Integer
    nChars = "<>|\/|*?:{}"
    ValidFilename = p_file
    For i = 1 To Len(nChars)
      ValidFilename = Replace(ValidFilename, Mid(nChars, i, 1), "", 1, -1, vbBinaryCompare)
    Next i
End Function

In this line of code, add the characters you want to remove

 nChars = "<>|\/|*?:{}"

Code goes in the sheet you want to change.

 

[Ajayjati25]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    Application.EnableEvents = False
    Target.Value = ValidFilename(Target.Value)
    Application.EnableEvents = True
End Sub
Function ValidFilename(p_file As String) As String
    Dim nChars As String
    Dim i As Integer
    nChars = "<>|\/|*?:{}"
    ValidFilename = p_file
    For i = 1 To Len(nChars)
      ValidFilename = Replace(ValidFilename, Mid(nChars, i, 1), "", 1, -1, vbBinaryCompare)
    Next i
End Function

In this line of code, add the characters you want to remove

 nChars = "<>|\/|*?:{}"

Code goes in the sheet you want to change.

Sir

Where should i use this code.
as in my excel file address is in 2nd column. Please find attached herewith sample of my excel data files. File name is also changing as per geneations of files. So please help

 

[Belleke]

See attached.
Code works for colomn B (see explanation in the code)
Use Alt + F11 to see the code in sheet 1.
See the green lines for some explanation.

 

[Ajayjati25]

Dear Sir

Thank you so much Its Working sir

 

[Belleke]

Thanks for the feedback.