SplitConcatList

SplitConcatList.txt

/** SplitConcatList */
/*
    FUNCTION NAME: SplitConcatList
    DESCRIPTION: Take a 1-column range of concatenated values and returns a 
        x rows by y columns array of split values, splitting the values in
		each row by the specified delimiter. Assumes that the number of 
		columns in the first row of the rng argument is the total number of
		columns throughout
    ARGS:
        rng: a 1-column wide range with the concatenated values
        delim: the delimiter used to split concatenated values into columns

    EXAMPLE: 
        =SplitConcatList(A1:A100,"|")
*/
SplitConcatList = LAMBDA(
    rng,
    delim,
    LET(
        num_rows,
        ROWS(rng),
        num_cols,
        COLUMNS(
            TEXTSPLIT(
                INDEX(
                    rng,
                    1
                ),
                delim,
                ,
                FALSE
            )
        ),
        MAKEARRAY(
            num_rows,
            num_cols,
            LAMBDA(
                r,
                c,
                INDEX(
                    TEXTSPLIT(
                        INDEX(
                            rng,
                            r
                        ),
                        delim,
                        ,
                        FALSE
                    ),
                    c
                )
            )
        )
    )
)