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

Thanks,

--
sandyboy

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

"sandyboy" wrote:

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

Thanks,

--
sandyboy

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"#",""),"\"," "),"@","")


"sandyboy" wrote:

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

Thanks,

--
sandyboy