Excel text functions

Text

_search = LAMBDA(txt, searchtext, [case_sensitive],
    // Test the inputs for errors so that we can distinguish
    // the error that comes from FIND/SEARCH as meaning "not-found".
    IFS(
        ISERROR(txt),
        txt,
        ISERROR(searchtext),
        searchtext,
        ISERROR(case_sensitive),
        case_sensitive,
        TRUE,
        LET(
            case_sensitive, IF(ISOMITTED(case_sensitive), FALSE, case_sensitive),
            IFERROR(IF(case_sensitive, FIND(searchtext, txt), SEARCH(searchtext, txt)), -1)
        )
    )
);

/**
 * Tests whether text contains with search_text.
 * Defaults to case insensitive.
 */
contains = LAMBDA(txt, searchtext, [case_sensitive],
    _search(txt, searchtext, case_sensitive) <> -1
);

/**
 * Tests whether text starts with search_text.
 * Defaults to case insensitive.
 */
startsWith = LAMBDA(txt, search_text, [case_sensitive],
    _search(txt, search_text, case_sensitive) = 1
);

/**
 * Tests whether text ends with search_text.
 * Defaults to case insensitive.
 */
endsWith = LAMBDA(txt, search_text, [case_sensitive],
    _search(txt, search_text, case_sensitive) = (LEN(txt) - LEN(search_text)) + 1
);

/**
 * Returns the range of text starting at position `start`
 * and end at position `end` (exclusive). IF `end` is omitted
 * the rest of the text is returned.
 */
range = LAMBDA(txt, start, [end],
    IF(
        ISOMITTED(end),
        MID(txt, start, LEN(txt) - start),
        LET(pos, MIN(start, end), end, MAX(start, end), MID(txt, pos, end - pos))
    )
);

/**
 * Reverse input text
 */
reverse = LAMBDA(txt,
    LET(
        textLen, LEN(txt),
        IF(txt = "", "", CONCAT(MID(txt, SEQUENCE(textLen, 1, textLen, -1), 1)))
    )
);

/**
 * Splits the input text into a column of letters.
 */
letters = LAMBDA(txt, MID(txt, SEQUENCE(LEN(txt)), 1));