I want to write a macro that will remove the special characters from column A and move the results to column B. Below is my task and my expected results in red:
Task Result
&Tim Tim
Lance**^ Lance
Amory Amory
Edy Edy
Frank Frank
Jack$!^ Jack
Mary 4* Mary 4
Matt!@ Matt
Sindy%^ Sindy
Saddy%is Saddyis
The result of what I wanted is in red.
I have started it but a bit confused. Below is my code so far. Please note that the data is more than 200 rows. I will appreciate it if someone helps me out. Thanks in advance for your time.
Sub remove_char()
Dim cleantxt As String, txt As String, a As Long
With Sheets("Sheet3").Range("A1", Range("a" & Rows.Count).End(xlUp).Resize(, 1))
With CreateObject("VBScript.RegExp")
.Pattern = "[^0-9a-zA-Z]"
.Global = True
.IgnoreCase = True
cleantxt = Application.Trim(.Replace(txt, ""))
End With
End If
End Sub
Bookmarks