copy cell less special char

[MartinL]

sorry for the confusing title

what I want to do is make B1 = A1 less any special characters

so If A1 = apple/green
I want B1 = "apple green"

special characters are any characters that windows will not accept as a save name
[FONT=Arial, Helvetica, sans-serif]* Asterisk[/FONT]
[FONT=Arial, Helvetica, sans-serif]< >[ ] Brackets[/FONT]
[FONT=Arial, Helvetica, sans-serif]= Equal sign[/FONT]
[FONT=Arial, Helvetica, sans-serif]+ Plus sign[/FONT]
[FONT=Arial, Helvetica, sans-serif]” Quotes[/FONT]
[FONT=Arial, Helvetica, sans-serif]\ / Slashes[/FONT]
[FONT=Arial, Helvetica, sans-serif], . : ; Comma, Period, Colon, Semicolon[/FONT]

 

[Andrew Poulsom]

There's a UDF here:

http://www.vbusers.com/code/codeget.asp?ThreadID=578&PostID=1

 

[JamesW]

Hi there,

Try (Thanks for bigresource):

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?""<>|"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

 

[MartinL]

thanks guys I was going down this route

=IF(ISERROR(REPLACE(C31,FIND({"*","<",">","[","]","=","+","\","/"},C31),1,"")),C4,REPLACE(C31,FIND({"*","<",">","[","]","=","+","\","/"},C31),1,""))

 

[GlennUK]

You could use a formula, once you've installed MOREFUNC add-in:

http://xcell05.free.fr/downloads/Morefunc.zip

... an array formula, entered using Ctrl-Shift-Enter , like:

=TRIM(MCONCAT(IF((ISERROR(MATCH(MID(A1,ROW(INDIRECT("A1:A99")),1),{"[","]","<",">","=","+","""","/","\",",",".",":",";","*"},0))),MID(A1,ROW(INDIRECT("A1:A99")),1)," ")))

.. changing MID(A1 to point to whatever cell ref ( remember to change both of them in the formula ).

 

[daverunt]

Hi,

there's this method but I've only added 3 of the characters you need to remove - I'm sure you can complete it if it's suitable.

=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"\",""),";",""),":",""))

 

[GlennUK]

Hi,

there's this method but I've only added 3 of the characters you need to remove - I'm sure you can complete it if it's suitable.

=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"\",""),";",""),":",""))

... only in Excel 2007 or higher, as there are more than 7 items to substitute.

 

[daverunt]

Thanks Glenn I'll try and remember that.