More than i substitution

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

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
 
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
 
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
 
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
Click to expand...

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
 
Peo, Pete, Ron
Thanks for your help. That makes what I want to do much easier. 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.
 
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.
Click to expand...

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 said:
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
Click to expand...
 
Amen. Otto
Click to expand...

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
 
Back
Top