Some text manipulation functions

excel_text_lambdas.txt

/* split_text
Apply the Excel TEXTSPLIT function down more than one row. For instance, copy paste 
the output of a series of arrays  from Jupyter to Excel. Each array
starts with a '[' character, the numbers are split by spaces and
end with a ']' character. We want to convert:

[0.1050748  0.04837582 0.02369428 0.00918702 0.0065269 ] 0
[0.08102541 0.05405571 0.02647627 0.01026568 0.00729323] 0

to the following across individual cells

0.1050748	0.04837582	0.02369428	0.00918702	0.0065269
0.08102541	0.05405571	0.02647627	0.01026568	0.0072932

Args: 
    text_: the text to convert. This must comprise records of the]
    same length, with a common delimiter used
    field_delimiter_: The separator for each field
    start_: the number of characters to drop from each row
    end_: the number of characters to drop from the end of each row

Return:
    The equivalent of the Excel 'Text to Columns' tool

Example:
    Suppose the two lines of text in the example above are in A2:A3

        split_test(A2:A3, " ", 2, 3)

Example inspired by Prashant at [pkv](https://excelmee.com/excel-formulas/textsplit-function-in-excel-365/)

This works first by joining all the text using a
column delimiter, then using TEXTSPLIT to split the combined string
by the defined column and row delimiters.
*/
split_text = LAMBDA(text_, field_delimiter_, start_, end_,
    LET(
        row_delimiter_, "~",
        col_delimiter_, "|",
        TEXTSPLIT(
            TEXTJOIN(
                row_delimiter_,
                TRUE,
                BYROW(
                    text_,
                    LAMBDA(row,
                        TEXTJOIN(
                            col_delimiter_,
                            TRUE,
                            TEXTSPLIT(
                                MID(
                                    row,
                                    start_,
                                    LEN(row) - end_ - start_
                                ),
                                field_delimiter_,
                                ,
                                TRUE
                            )
                        )
                    )
                )
            ),
            col_delimiter_,
            row_delimiter_
        )
    )
);