An interative Recursive Array formula (excel only)

[Spyros13]

I have [370, 380, 281, 484, …] in Cell C4.

I want to add 2 to each number separated by the columns, and do so without using VBA or Python ,

So, in D4 I can do Rick Rothenstiens array formula ,

={SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($C$3,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999)),"[",""),",","")+2}

& it extracts each number out when i enter as Array I can drag across.

And that gives 372, 382, 283, 485, .. etc etc, in different cells accross the adjacent range of columns. Then I can concatenate the ranges using TextJoin (but I don't have MS O 360), so I used any number of the available concatenate cells UDF's I have. Great.

No. That still leaves me with messy helper columns either in the main sheet in working in, or in sheet2.

I would like an array formula which does the work to the 'cell' (sorry, should call it array element or extracted string/number) of adding 2 in this case, all outputted in D4. So D4 looks like this:

[372, 382, 283, 485, ...] straight away after CSE.

p.s. The numbers seperated by delimanator (commas in this case) are NOT always 3 digits in length (can be 5 or 6 or 9 too)

the closest NON-VBA way I have got to this is very cave-man like and combersome (although If i have enough for each delimanation in column C, I can just simply drag down), but its messy imo and un-pythonic/un-excelly if you excuse the phrase.

Yep, what I did, so far is :

=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($C$3,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999)),"[",""),",","")+2&", "&SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($C$3,",",REPT(" ",999)),COLUMNS($A:B)*999-998,999)),"[",""),",","")+2&", "&SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($C$3,",",REPT(" ",999)),COLUMNS($A:C)*999-998,999)),"[",""),",","")+2&" , "&SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($C$3,",",REPT(" ",999)),COLUMNS($A:D)*999-998,999)),"[",""),",","")+2

I should be embarassed. But Im not. I am asking for help.

I tried using

CHOOSE(COLUMNS($A:C),1,2,3)*999-998,999)

, got something of an inkling of how it could work ,in the highlighted red bit, to mimick an iteration through all those columns it would have to go through if it was a traditional array, so it does them all at once in once cell, but my know-how wasn't good enough. But implementing choose might be the way to go.

Going to bed, If anyone cal help if be grateful.

Anyone know?/Can help?

 

[Spyros13]

Rick, You have helped me immensely & I appreciate it ! I've already done something with your udf , and shall be doing more, and will be using it a lot more going forward. So Thank you ! Grateful! Grateful Thank you !