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]

p.p.s. using a concatonate function on the array on its own in a single cell (as i foolishly tried at first) wont work because the array is yet to be calculated (as the answer/solution is calculated by moving the array accross multiple columns first), all youll get is the first value (or which ever value is in the column you using concat, on).

I need an array-of-an-array formula for this. I think it would involve choose.

Its a bit like the non-coding equivalent of a whilst-do loop, or for each column selection do loop.

This all can be done with coding easily, But I am looking for an Excel formula version only.

 

[shaowu459]

Book1
	C
3	[370,380,281,484]
4	[372,382,283,486]
Sheet1

Cell Formulas
Range	Formula
C4	C4	="["&TEXTJOIN(",",,IFERROR(2+TRIM(MID(SUBSTITUTE(MID(LEFT($C$3,LEN(C3)-1),2,999),",",REPT(" ",999)),COLUMN($A:$Z)*999-998,999)),""))&"]"

 

[Spyros13]

I would like it , but I don't have textjoin on my excel. Thank you.

 

[shaowu459]

I would like it , but I don't have textjoin on my excel. Thank you.

Sorry, i missed the information in your post you do not have office 365.

 

[Spyros13]

No Im sorry, I should have been clearer.

Office 365 must work very differently, because even the new array you made doesn't calcululate properly on ''''normal''' excel (2010).

Using 2+TRIM(MID(SUBSTITUTE(MID(LEFT($C$3,LEN(C3)-1),2,999),",",REPT(" ",999)),COLUMN($A:$Z)*999-998,999)) alone , gives me the 1st value but errors for all the others, Im using Windows 10, MS excel latest one before 360 came out.

 

[Rick Rothstein]

I know you don't want to use VBA, but if you ever resign yourself to doing so, here is a UDF (user defined function) that you can use. The first argument is how much you want to add to the values and the second argument is either a text string or a cell reference (simple address when used as a UDF).

Function AddDelimText(HowMuch As Long, S As String) As String
  Dim X As Long, Arr As Variant
  Arr = Split(S, ",")
  For X = 0 To UBound(Arr)
    Arr(X) = Arr(X) + HowMuch
  Next
  AddDelimText = Join(Arr, ",")
End Function

An example of its use for your stated parameters would be...

=AddDelimText(2,C4)

 

[Spyros13]

Very funny, & , very clever! Thank you. It works & It will come in use , im definately sure, of that but yes oliver prefers an excel function. But thank you. Very Cool, clever and neat.

 

[Spyros13]

I think I underestimated the importance of Rick Rothsteins UDF to my life , So Big Thanks !!!! Thank You Rick !! Genius, Thank you & Bow.

 

[Rick Rothstein]

That was a pretty enthusiastic posting on your part... I really appreciate it. And, of course, you are quite welcome... I am glad I was able to have been of some help.