"SUBSTITUTE" Function With Wilcard For Symbols?

[Dhira]

In trying to create a formula that remove EVERY symbol from a cell (C6) and replacing it with a space.... the "SUBSTITUTE" function is telling me I have too many nested Substitutes.

This is the formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([COLOR=Red]C6[/COLOR],"/"," "),"%"," "),"!"," "),","," "),"*"," "),"-"," "),"("," "),")"," ")

It works thus far, but I would still like to add more symbols into the nest.
Is there a way/formula to do this that replaces ANY symbol with a space?

 

[Gerald Higgins]

What do you define as a symbol ?

 

[p45cal]

perhaps a udf such as this:

Excel Workbook
	C	D
18	P/()-*J,!%D	P J D
Sheet

backed up by a function:

Function RemoveStuff(FromWhatString, CharactersToRemove, CharactersToPutInstead)
For i = 1 To Len(CharactersToRemove)
  FromWhatString = Replace(FromWhatString, Mid(CharactersToRemove, i, 1), CharactersToPutInstead, , , vbTextCompare)
Next i
RemoveStuff = FromWhatString
End Function

(Excel Jeanie or this site seems to absorb the spaces in D18)

 

[Dhira]

What do you define as a symbol ?

Anything on the number keys as well as the following in red:
",", ".", "(",")", "/", "\", "|", "<", ">", "~", "'", "`"

I guess anything that's not a letter.

 

[Dhira]

p45cal,

How would I set that function in the spreadsheet?
Thanks.

 

[T. Valko]

In trying to create a formula that remove EVERY symbol from a cell (C6) and replacing it with a space.... the "SUBSTITUTE" function is telling me I have too many nested Substitutes.

This is the formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C6,"/"," "),"%"," "),"!"," "),","," "),"*"," "),"-"," "),"("," "),")"," ")

It works thus far, but I would still like to add more symbols into the nest.
Is there a way/formula to do this that replaces ANY symbol with a space?

If you're using Excel 2003 or earlier then you can only nest 8 functions as you have discovered.

Not the optimal solution but one that will work...

Enter the above formula in a cell then refer to that cell in another formula that removes additional characters.

Someone may be able to come up with a UDF that can do it but you'd have to let them know EXACTLY what all of the characters are to be removed or, what characters are to be saved.

 

[p45cal]

p45cal,

How would I set that function in the spreadsheet?
Thanks.

I've shown the formula you would use in D18.
To add the code, press Alt+F11, then using the menus, Insert|Module, paste the code where the flashing cursor is.
Go back to the sheet, recalculate or re-enter the formula. If it gives the right answer, save the workbook and close the newly opened vb editor.
If you get an error Variable not defined, either remove Option Explicit from the top of the module or add
Dim i as Long
as the first line under Function....

If, on the sheet when you're entering the formula, you click on fx on the formula bar, it will be more obvious what to enter.

 

[Biz]

Try using

Function ReplaceSpecial(ByVal Txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "[^A-Z\s0-9]+"
    .Global = True
    .IgnoreCase = True
    ReplaceSpecial = Application.Trim(.Replace(Txt, ""))
End With
End Function

Formula cell D18
=SUBSTITUTE(ReplaceSpecial(C18)," ","")

Biz

 

[Joseph.Marro]

Dhira,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If you have the morefunc add-in you could use regular expressions. The following formula will remove all characters that are not a number, letter or space.<o></o>
<o></o>
=REGEX.SUBSTITUTE(C6,"[^ \w]"," ")<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>

 

[Dhira]

p45cal & Biz,

Thank you. That did work, however I had to remove the function because I kept on getting a Privacy warning every time I tried to save - incredibly annoying It wouldn't go away even when I changed settings in the trust center (Excel 2007) SO I had to try Joseph's :

Dhira,<!--?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /--><o></o>
<o></o>
If you have the morefunc add-in you could use regular expressions. The following formula will remove all characters that are not a number, letter or space.<o></o>
<o></o>
=REGEX.SUBSTITUTE(C6,"[^ \w]"," ")<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>

And it works a treat.
(Had to download morefunc at DOwnload.com since it wasn't available)

Thanks again.