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
--
David

Hi David

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

Regards

Roger Govier


David wrote:
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
--
David

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
--
David

"Roger Govier" wrote:

Hi David

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

Regards

Roger Govier


David wrote:
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
--
David

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


David wrote:
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
--
David

"Roger Govier" wrote:


Hi David

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

Regards

Roger Govier


David wrote:

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
--
David