Eliminate the need for Nested Substitute

**ElmerS** · 04-09-2010, 03:22 PM

Hi,

Assume A1: ABCDABCDABCD

If I want to delete all "A"s and "D"s I would use: =SUBSTITUTE(SUBSTITUTE(A1,"A",""),"C","")

and get: BDBDBD which is fine

My question:

Is there a way to use SUBSTITUTE only once ?

Something like: =SUBSTITUTE(A1,{"A";"C"},"")

I tried it, with no success.

All I'm looking for is a WS build-in function.

Thanks, Elm

**shg** · 04-09-2010, 03:38 PM

No; array-entered, the second function returns two results, one deleting As and one deleting Cs. You need the results of one invocation returned to the second.

**DonkeyOte** · 04-09-2010, 04:14 PM

Just reiterating shg's point - no, not really, nothing practical at any rate.

I asked a similar question at MrExcel.com sometime back which may/may not be of interest - you might like PGC01's response however (post #19)

http://www.mrexcel.com/forum/showthread.php?t=332284

**ElmerS** · 04-09-2010, 04:41 PM

Thanks.

It was interesting to read through but I had a HUGE "Déjà vu" reading Oaktrees response (quote):

"No VBA, no add-ins, no nesting SUBSTITUTEs.... you can't use the letter A in your formula..."

I hope you understand what I mean by that quoting.

Elm