Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than i substitution
For more than that, you can use VBA routines. --ron Well, you could continue into another column, Ron. Pete |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
substitution: a better method? | Excel Worksheet Functions | |||
Substitution of words | Excel Discussion (Misc queries) | |||
Substitution | Excel Discussion (Misc queries) | |||
substitution | Excel Discussion (Misc queries) | |||
Substitution | Excel Discussion (Misc queries) |