Array formula help please
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 |
Array formula help please
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 |
Array formula help please
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 |
Array formula help please
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 |
All times are GMT +1. The time now is 08:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com