remove strange characters from one column 1

[tlpeter]

Hi all

I was pointed overhere for this

How can remove strange characters from one column ?
I have found a code that works but it does on all cells

I just need it on one column
I tried searching but could not find anything related

I am a noob with VBA so i hope someone can help me with it
This code is what i already have but i need it for just one cell

[qode]Private Sub Worksheet_Change(ByVal Target As Range)
Cells.Replace what:="@", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:="#", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:="$", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:="%", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:="^", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:="&", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:="(", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:=")", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:=":", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:=";", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:="'", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:="-", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace what:=" ", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub
[/code]

ACA - Implement IP Office
ACS - Implement IP Office
ACA - Voice Services Management
______________
Women and cats can do as they please and men and dogs should relax and get used to the idea!

 

[SkipVought]

Hi,

Replace Cells with Columns(YourColumnNumber)

Do not perform within the Worksheet_Change event. Run your code in a new module.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[PHV]

You may try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> [i]yourColumnnumber[/i] Then
  Exit Sub
End If
Application.EnableEvents = False
Target.Replace what:="@", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:="#", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:="$", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:="%", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:="^", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:="&", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:="(", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:=")", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:=":", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:=";", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:="'", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:="-", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Target.Replace what:=" ", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Application.EnableEvents = True
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tlpeter]

That did the trick

I could not get Skip his way working but i must have done something wrong


ACA - Implement IP Office
ACS - Implement IP Office
ACA - Voice Services Management
______________
Women and cats can do as they please and men and dogs should relax and get used to the idea!