Array formula help please

[Guest]

Greetings,
I'm looking fo an array formula to substitue specified charasters with an
underscore. I've tried:
=SUBSTITUTE(MyRange,{" ","&","(",")"},{"_","_","_","_"}) Ctrl+Shift+Enter
This works for any instances of the first character (space) but ignores any
of the others.
Any advice will be appreciated
TIA

 

[Roger Govier]

Hi David

Try using the non-array entered
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(myRange,"
","_"),"&","_"),"(","_"),")","_")

Regards

Roger Govier

 

[Guest]

Thanks Roger,
You formula works fine. This will form part of a 'mega' formula. I thought
of using an array formula to make the sheet more readable. Still wondering if
that is possible?
Thanks again

 

[Roger Govier]

Hi David

I couldn't see the way to do it as an array, but you bet there's someone out
there who can.

If I end up with mega formulae, I "cheat" and use helper cells with parts of
the formula (in hidden columns if necessary) or put some of the formula in a
defined Name. Makes it much easier to maintain.

Regards

Roger Govier

Thanks Roger,
You formula works fine. This will form part of a 'mega' formula. I thought
of using an array formula to make the sheet more readable. Still wondering if
that is possible?
Thanks again