Text Manipulation LAMBDA Functions

TextManipulation.lambda

/*
Excel Text Manipulation LAMBDA functions
Courtesy of Excel Robot

The Microsoft Excel team recent released three new Excel functions for breaking apart text by delimiters.
Since the new functions are only available to users running Beta Channel, I've created my own versions and 
given them the same function names so anyone with LAMBDA and LAMBDA helper functions (like MAKEARRAY) can 
import these LAMBDAs into your own workbooks and have nearly the same functionality.

* Feel free to share this collection, appreciate any shout outs to Excel Robot along the way.

Text Manipulation:
TEXTBEFORE - https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29
TEXTAFTER - https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4
TEXTSPLIT - https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7

*/

/*
Name: Get Text Before Substring (TEXTBEFORE)
Description: Extracts the text before the Nth instance of a specified substring, optionally ignoring case. 
   Specifying a negative number for N will use Nth instance from the end of string instead of beginning.
Contributed By: Excel Robot (@ExcelRobot)
Category: String
*/
TEXTBEFORE = LAMBDA(input_text,text_before,[n],[ignore_case],LET(
    NthInstance, IF(ISOMITTED(n),1,n),
    IgnoreCase, IF(ISOMITTED(ignore_case),TRUE,ignore_case),
    ColEscapeChar, CHAR(134),
    EscapeDelimiter, IF(IgnoreCase,
        SUBSTITUTE(
            REDUCE("",MID(input_text,sequence(LEN(input_text)),1),
                LAMBDA(s,a,
                    IF(RIGHT(s&a,LEN(text_before))=text_before,
                        LEFT(s&a,LEN(s&a)-LEN(text_before))&text_before,
                        s&a
                        )
                    )
                ),
            text_before,
            ColEscapeChar
            ),
        SUBSTITUTE(input_text,text_before,ColEscapeChar)
        ),
    Letters, MID(EscapeDelimiter,SEQUENCE(,LEN(EscapeDelimiter)),1),
    IsColDelimiter, --(Letters=ColEscapeChar),
    ColCount, MAX(SCAN(0,IsColDelimiter,LAMBDA(s,a,IF(a=-1,0,s+a))))+1,
    ColIndex, SCAN(1,IsColDelimiter,LAMBDA(s,a,IF(a=0,s,IF(a=-1,1,s+1)))),
    SplitValues, MAKEARRAY(1,ColCount,
        LAMBDA(i,j,CONCAT(FILTER(Letters,(ColIndex=j)*(IsColDelimiter=0),"")))),
    FilterValues, IFS(
        NthInstance>0, TEXTJOIN(text_before,FALSE,
            FILTER(SplitValues,SEQUENCE(1,COLUMNS(SplitValues))<=NthInstance)
            ),
        NthInstance<0, TEXTJOIN(text_before,FALSE,
            FILTER(SplitValues,
                SEQUENCE(1,COLUMNS(SplitValues),COLUMNS(SplitValues),-1)>ABS(NthInstance))
            ),TRUE,""),
    Result, IFS(
        LEN(text_before)=0, NA(),
        NthInstance=0, ""*0,
        ABS(NthInstance)>COLUMNS(SplitValues)-1, NA(),
        LEN(FilterValues)=0, "",
        TRUE, LEFT(input_text,LEN(FilterValues))
        ),
    Result
));

/*
Name: Get Text After Substring (TEXTAFTER)
Description: Extracts the text after the Nth instance of a specified substring, optionally ignoring case.
   Specifying a negative number for N will use Nth instance from the end of string instead of beginning.
Contributed By: Excel Robot (@ExcelRobot)
Category: String
*/
TEXTAFTER = LAMBDA(input_text,text_after,[n],[ignore_case],
LET(
    NthInstance, IF(ISOMITTED(n),1,n),
    IgnoreCase, IF(ISOMITTED(ignore_case),TRUE,ignore_case),
    ColEscapeChar, CHAR(134),
    EscapeDelimiter, IF(
        IgnoreCase,
        SUBSTITUTE(
            REDUCE("",MID(input_text,sequence(LEN(input_text)),1),
                LAMBDA(s,a,
                    IF(
                        RIGHT(s&a,LEN(text_after))=text_after,
                        LEFT(s&a,LEN(s&a)-LEN(text_after))&text_after,
                        s&a
                        )
                    )
                ),
            text_after,
            ColEscapeChar
            ),
        SUBSTITUTE(input_text,text_after,ColEscapeChar)
        ),
    Letters, MID(EscapeDelimiter,SEQUENCE(,LEN(EscapeDelimiter)),1),
    IsColDelimiter, --(Letters=ColEscapeChar),
    ColCount, MAX(SCAN(0,IsColDelimiter,LAMBDA(s,a,IF(a=-1,0,s+a))))+1,
    ColIndex, SCAN(1,IsColDelimiter,LAMBDA(s,a,IF(a=0,s,IF(a=-1,1,s+1)))),
    SplitValues, MAKEARRAY(
        1,
        ColCount,
        LAMBDA(i,j,CONCAT(FILTER(Letters,(ColIndex=j)*(IsColDelimiter=0),"")))
        ),
    FilterValues, IFS(
        NthInstance > 0, TEXTJOIN(
            text_after,
            FALSE,
            FILTER(SplitValues,SEQUENCE(1,COLUMNS(SplitValues))>NthInstance)
            ),
        NthInstance < 0, TEXTJOIN(
            text_after,
            FALSE, FILTER(
                SplitValues,
                SEQUENCE(
                    1,
                    COLUMNS(SplitValues),
                    COLUMNS(SplitValues),
                    -1
                    )<=ABS(NthInstance)
                )
            ),
        TRUE,""
        ),
    Result, IFS(
        LEN(text_after)=0, NA(),
        NthInstance=0, ""*0,
        ABS(NthInstance)>COLUMNS(SplitValues)-1, NA(),
        LEN(FilterValues)=0, "",
        TRUE, RIGHT(input_text,LEN(FilterValues))
        ),
    Result
));

/*
Name: Split Text By Delimiters (TEXTSPLIT)
Description: Separates text by one or more column delimiters and/or row delimiters.  Optionally 
   excludes empty cells created by consecutive delimiters.  Optionally, extra cells created by 
   inconsistent number of columns per row can be filled with a specified value (default #N/A!).
   Note: No more than one column delimiter or row delimiter can be multiple characters.
Contributed By: Excel Robot (@ExcelRobot)
Category: String
*/
TEXTSPLIT = LAMBDA(text, col_delimiter, [row_Delimiter], [ignore_empty], [pad_with],LET(
    ColDelimiters, IF(COLUMNS(col_delimiter)>1,TRANSPOSE(col_delimiter),col_delimiter),
    RowDelimiters, IFS(
        ISOMITTED(row_delimiter), "",
        COLUMNS(row_delimiter)>1, TRANSPOSE(row_delimiter),
        TRUE, row_delimiter
        ),
    MultiCharColDelimiter, IFERROR(INDEX(FILTER(ColDelimiters,LEN(ColDelimiters)>1),1),""),
    MultiCharRowDelimiter, IFERROR(INDEX(FILTER(RowDelimiters,LEN(RowDelimiters)>1),1),""),
    ColEscapeChar, CHAR(134),
    RowEscapeChar, CHAR(135),
    PadWith, IF(ISOMITTED(pad_with),NA(),pad_with),
    RemoveDuplicateDelimiters, IF(ISOMITTED(ignore_empty),text,
        IF(ignore_empty,REDUCE(text,ColDelimiters,
            LAMBDA(new_text,delim,
                REDUCE(new_text,SEQUENCE(
                    ROUNDUP(((LEN(new_text)-LEN(
                        SUBSTITUTE(new_text,delim&delim,delim)
                        ))/LEN(delim))*1.5,0
                        )),
                    LAMBDA(s,a,SUBSTITUTE(s,delim&delim,delim))
                    )
                )
            ))
        ),
    EscapeMultiCharDelimiters, SUBSTITUTE(
        SUBSTITUTE(RemoveDuplicateDelimiters,MultiCharColDelimiter,ColEscapeChar),
        MultiCharRowDelimiter,
        RowEscapeChar
        ),
    Letters, MID(EscapeMultiCharDelimiters,SEQUENCE(,LEN(EscapeMultiCharDelimiters)),1),
    IsColDelimiter, --((BYCOL(--(Letters=ColDelimiters)*SEQUENCE(ROWS(ColDelimiters)),
        LAMBDA(delim_match,MAX(delim_match)))+(Letters=ColEscapeChar))>0
        ),
    IsRowDelimiter, --((BYCOL(--(Letters=RowDelimiters)*SEQUENCE(ROWS(RowDelimiters)),
        LAMBDA(delim_match,MAX(delim_match)))+(Letters=RowEscapeChar))>0
        ),
    ColCount, MAX(SCAN(0,IsColDelimiter-IsRowDelimiter,LAMBDA(s,a,IF(a=-1,0,s+a))))+1,
    RowCount, IF(ISOMITTED(row_Delimiter),1,SUM(IsRowDelimiter)+1),
    ColIndex, SCAN(1,IsColDelimiter-IsRowDelimiter,LAMBDA(s,a,IF(a=0,s,IF(a=-1,1,s+1)))),
    RowIndex, SCAN(1,IsRowDelimiter,LAMBDA(s,a,IF(a=0,s,s+1))),
    Result, MAKEARRAY(RowCount,ColCount,
        LAMBDA(i,j,CONCAT(FILTER(
            Letters,
            (ColIndex=j)*(RowIndex=i)*(IsColDelimiter=0)*(IsRowDelimiter=0),
            PadWith)
            ))
        ),
    Result
));