Announcing New Text and Array Functions

 jignesh0yt2k 

This is just part of the ongoing battle with the new functionality (Lambda helper functions especially), coercing them to deal adequately with problems involving 'arrays of arrays' (that is normal spreadsheet models).  If, as in Hitchhikers Guide to the Galaxy, the answer to your problem is a scalar (e.g. 42), you are OK.  If it is a single column of numbers, you are also almost certainly well supported.

It is when your intended output is a block of numbers (a 2D array, often representing an array of arrays) that you may well be in trouble.  Sometimes Excel will return what you want, typically by 'lifting' arguments or 'broadcasting' but, more often, there is the dreaded #CALC! error with an unhelpful message such as 'Nested Arrays are not supported'.  There are workarounds which apply under varying circumstances. 

REDUCE/VSCAN is reasonably general, but grinds to a halt with increasing array size.  In this case, reformulating the problem as a single string can help, but hits limits on string length.  Recursive bisection can be made to work; that is halving the array until only two lines remain and combining them with VSTACK (then combine two pairs and so on).  Using thunks can produce results (instead of an array of arrays, one develops an array of functions for later evaluation - it can be made to work but is beyond most peoples pay-grade!

Again, in the present circumstance, one could use MAP to open each comma-separated string in turn and convert it to a string of fixed-length substrings.  The old text function MID will return and array of arrays by lifting the 'start' parameter so

//Worksheet formula
= MID(
      MAP(list, ConvertToFixedλ), 
      SEQUENCE(1,2,1,32), 
      32
  )

//Lambda function
ConvertToFixedλ
= LAMBDA(string,
    LET(
      items,   TEXTSPLIT(string, ","),
      padding, REPT(" ", 32),
      fixed,   CONCAT(LEFT(items & padding, 32)),
      fixed
    )
  )

works just fine.

The point is that I do not wish to become the world expert on 'tricks and workarounds' for the Excel 'array of arrays' problem!

I simply want Excel to return the obvious solution to each problem just by doing what it says on the tin.  That way, I can recommend the new functionality without reservation, knowing that I am not leading the unwary up the garden path, only for them to hit a dead end (maybe got my metaphors somewhat tangled there).