Using TEXTSPLIT without copy-fill-down

The array of arrays should not be a problem; it is simply the typical output from array calculation.  Since the original date of this post, I have written helper functions (with a bit of tidying up from djclements​ ) that return arrays of arrays.

= MAPλ(values, LAMBDA(values, TEXTSPLIT(values, ";")))

or by Currying the LAMBDA function

Worksheet formula
= MAPλ(values, TEXTSPLITλ(";"));
where
TEXTSPLITλ
= LAMBDA(s, LAMBDA(v, TEXTSPLIT(v, s)));

The function MAPλ is to be found at

A version of Excel MAP helper function that will return an array of arrays

Hi, isn't the following more simple?

=HSTACK(Table2['#],IFERROR(TEXTSPLIT(TEXTJOIN("|",,Table2[Values]),";","|"),""))

=HSTACK(Table2['#],IFERROR(TEXTSPLIT(TEXTJOIN("|",,Table2[Values]),";","|"),""))

dipankar_de Here's an updated variant, using a TEXTBEFORE/TEXTAFTER based LAMBDA function:

=HSTACK(Table2['#], TEXTTOCOLS(Table2[Values], "; "))

- OR -

=TEXTTOCOLS(Table2['#]&"; "&Table2[Values], "; ")

Where TEXTTOCOLS is defined in Name Manager as:

=LAMBDA(array,delimiter,
    LET(
        delims, (LEN(array)-LEN(SUBSTITUTE(array, delimiter, )))/LEN(delimiter),
        cols, MAX(delims)+1,
        arr, delimiter&array&REPT(delimiter, cols-delims),
        TEXTBEFORE(TEXTAFTER(arr, delimiter, SEQUENCE(, cols)), delimiter)
    )
)

This method is similar to dscheikey's solution in that it avoids the character limitations imposed on TEXTJOIN and CONCAT and is more efficient than the iterative alternatives, REDUCE/VSTACK and MAKEARRAY/INDEX, when working with larger datasets.

dipankar_de 

I'd simplify to

=LET(
   data, HSTACK( Table2['#],  TEXTSPLIT( TEXTJOIN("|",,Table2[Values]), ";","|",,,"") ),
   header, HSTACK( {"#","Key"}, "Value" & SEQUENCE(, COLUMNS(data) - 2 ) ),
   VSTACK( header, data )
 )

dipankar_de 

I think the abject failure to process arrays or arrays is the most ridiculous error of judgement made by Microsoft in what is otherwise a suburb sequence of innovations, turning Excel from amateurs' corner into something capable of creating reusable, quality solutions.  One of the principal benefits of the grid is that it allows one to calculate and display arrays of arrays.  To pretend otherwise is to store up huge compatibility problems for when sense eventually prevails. 

To offer an absurd solution, I developed a custom Lambda function "Repackλ" to turn the comma-deliminated values into fixed width strings

Repackλ 
= LAMBDA(t,
    LET(
        splitText, TEXTSPLIT(t, ","),
        length,    LEN(splitText),
        CONCAT(splitText & REPT(" ", 10 - length))
    )
);

giving

What appears to be a monumental waste of effort pays off because MID will generate and array of arrays if used to wrap the Lambda function.

= MID( MAP(Text, Repackλ), {1,11,21,31,41}, 10)

giving

Having to go back to MID to achieve something that 'Excel has never done?' is so annoying that I propose to continue expressing my scorn at every opportunity (and there are many opportunities, which makes the need for change so urgent).  I hope you all join in to make Microsoft listen even if just to save themselves further embarrassment!

dipankar_de 

Here's my take:

=LET(matrix,MAKEARRAY(ROWS(Table2),9,LAMBDA(r,c,CHOOSECOLS(EXPAND(TEXTSPLIT(INDEX(Table2[Values],r),";"),1,9,""),c))),HSTACK(Table2['#],matrix))

dipankar_de 

At first I thought that this could be solved quite simply with TEXTSPLIT() within BYROW(). But that always resulted in a CALC# error.

I then solved this rather oldscool with MID() SEARCH() and SEQUENCE().

There are probably other variants.

=LET(
in,SUBSTITUTE("; "&Table2[Values]&"; "," ",""),
maxi,MAX(LEN(in)-LEN(SUBSTITUTE(in,";","")))-1,
start,SEARCH(CHAR(124),SUBSTITUTE(in,";",CHAR(124),SEQUENCE(1,maxi,1))),
end,SEARCH(CHAR(124),SUBSTITUTE(in,";",CHAR(124),SEQUENCE(1,maxi,2))),
HSTACK(Table2['#],IFERROR(MID(in,start+1,end-start-1),"")))

I hope that's OK for you.