Special Characters

S

sandyboy

Is there a function in excel that removes special characters (ie: # \ @) from
a cell? Would appreciate prompt reply.

Thanks,
 
M

Mike H

Hi,

One way. Assumes the cells you want to remove these characters from are in
column A but change to suit. This will extract the characters that are in
cell J1 on the active sheet. The pattern provided extracts upper/lower case
letters, numbers and full stop. To add a character use the syntax |[?] to
extract the question mark.

Sub removespecial()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
.Pattern = Cells(1, 10).Value 'J1 on active sheet> [A-Z]|\d|[a-z]|[.]
End With
Set Myrange = ActiveSheet.Range("a1:a10") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
'C.Offset(0, 1) = Outstring
C.Value = Outstring
Next

Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
End Sub


Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top