More than i substitution
 

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

More than i substitution
 

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

More than i substitution
 

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

More than i substitution
 

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

More than i substitution
 

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

Well, you could continue into another column, Ron.

Pete

More than i substitution
 

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

More than i substitution
 

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

More than i substitution
 

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

More than i substitution
 

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

More than i substitution
 

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

More than i substitution
 

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