Excel XP & Win XP
I am using the Substitute function in conjunction with the Indirect function
in a Data Validation formula. My question is about the Substitute function.
Is there a way to substitute nothing for more than one thing in a single
formula? For instance, substitute nothing for all spaces, all commas, all &
symbols, etc, all in one formula? Thanks for your time. Otto

You will run into the nested limitations eventually but you can use more
than one substitute

spaces, commas and ampersands would look like this

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),"&","")




--


Regards,


Peo Sjoblom




"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I am using the Substitute function in conjunction with the Indirect
function in a Data Validation formula. My question is about the
Substitute function. Is there a way to substitute nothing for more than
one thing in a single formula? For instance, substitute nothing for all
spaces, all commas, all & symbols, etc, all in one formula? Thanks for
your time. Otto

Yes, just nest them, so the inner function changes spaces to blanks,
the next outer one changes commas to blanks etc. Be wary of the limit
of 7 nested functions if you are using XL2003 or earlier.

Hope this helps.

Pete

On Nov 19, 6:34 pm, "Otto Moehrbach" wrote:
Excel XP & Win XP
I am using the Substitute function in conjunction with the Indirect function
in a Data Validation formula. My question is about the Substitute function.
Is there a way to substitute nothing for more than one thing in a single
formula? For instance, substitute nothing for all spaces, all commas, all &
symbols, etc, all in one formula? Thanks for your time. Otto

On Mon, 19 Nov 2007 13:34:25 -0500, "Otto Moehrbach"
wrote:

Excel XP & Win XP
I am using the Substitute function in conjunction with the Indirect function
in a Data Validation formula. My question is about the Substitute function.
Is there a way to substitute nothing for more than one thing in a single
formula? For instance, substitute nothing for all spaces, all commas, all &
symbols, etc, all in one formula? Thanks for your time. Otto


You can nest SUBSTITUTE up to the Excel nesting limit of seven formulas (at
least through 2003; I don't know what the limit is in 2007).

For more than that, you can use VBA routines.
--ron

For more than that, you can use VBA routines.
--ron

Well, you could continue into another column, Ron.

Pete

On Mon, 19 Nov 2007 11:42:15 -0800 (PST), Pete_UK wrote:


For more than that, you can use VBA routines.
--ron

Well, you could continue into another column, Ron.

Pete

You could, but that would not satisfy the OP's request of "all in one formula".


--ron

Peo, Pete, Ron
Thanks for your help. That makes what I want to do much easier. Otto
"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I am using the Substitute function in conjunction with the Indirect
function in a Data Validation formula. My question is about the
Substitute function. Is there a way to substitute nothing for more than
one thing in a single formula? For instance, substitute nothing for all
spaces, all commas, all & symbols, etc, all in one formula? Thanks for
your time. Otto

You can nest 64 formulas in Excel 2007, though I doubt anyone would
ever want to. The formula length limit is about 8,000 characters.


On Nov 19, 2:36 pm, Ron Rosenfeld wrote:
On Mon, 19 Nov 2007 13:34:25 -0500, "Otto Moehrbach"
wrote:

Excel XP & Win XP
I am using the Substitute function in conjunction with the Indirect function
in a Data Validation formula. My question is about the Substitute function.
Is there a way to substitute nothing for more than one thing in a single
formula? For instance, substitute nothing for all spaces, all commas, all &
symbols, etc, all in one formula? Thanks for your time. Otto

You can nest SUBSTITUTE up to the Excel nesting limit of seven formulas (at
least through 2003; I don't know what the limit is in 2007).

For more than that, you can use VBA routines.
--ron

On Mon, 19 Nov 2007 12:33:12 -0800 (PST), iliace wrote:

You can nest 64 formulas in Excel 2007, though I doubt anyone would
ever want to. The formula length limit is about 8,000 characters.

That's quite a jump from seven.

For me, when nesting is more than a few deep, I find it simpler to just write
(and certainly simpler to debug) a VBA UDF.
--ron

Amen. Otto
"Ron Rosenfeld" wrote in message
...
On Mon, 19 Nov 2007 12:33:12 -0800 (PST), iliace
wrote:

You can nest 64 formulas in Excel 2007, though I doubt anyone would
ever want to. The formula length limit is about 8,000 characters.

That's quite a jump from seven.

For me, when nesting is more than a few deep, I find it simpler to just
write
(and certainly simpler to debug) a VBA UDF.
--ron

On Mon, 19 Nov 2007 17:23:31 -0500, "Otto Moehrbach"
wrote:

Amen. Otto
"Ron Rosenfeld" wrote in message
.. .
On Mon, 19 Nov 2007 12:33:12 -0800 (PST), iliace
wrote:

You can nest 64 formulas in Excel 2007, though I doubt anyone would
ever want to. The formula length limit is about 8,000 characters.

That's quite a jump from seven.

For me, when nesting is more than a few deep, I find it simpler to just
write
(and certainly simpler to debug) a VBA UDF.
--ron


Here's a little sub that replaces any of a list of various tokens in a src
string. You can add to the replacement list by adding characters with in the
square brackets. This is just an example. Obviously you could rewrite it as a
function, or add code to have it work on a specific range; etc.

========================
Option Explicit
Sub foo()
Const s As String = "[,;:\s]"
'tokens to replace between [ ]
' \s = space
Const src As String = "Now is , the time for all ; :"

Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = s
re.Global = True

Debug.Print re.Replace(src, "")

End Sub
==============================

--ron