More than i substitution

[Otto Moehrbach]

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

 

[Peo Sjoblom]

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

 

[Pete_UK]

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

 

[Ron Rosenfeld]

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

 

[Pete_UK]

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

Well, you could continue into another column, Ron.

Pete

 

[Ron Rosenfeld]

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

 

[Otto Moehrbach]

Peo, Pete, Ron
Thanks for your help. That makes what I want to do much easier. Otto

 

[iliace]

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.

 

[Ron Rosenfeld]

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

 

[Otto Moehrbach]

Amen. Otto

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

 

[Ron Rosenfeld]

Amen. Otto

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