This Excel Lambda namespace contains several functions for working with text

excel-lambda-namespace-TEXT.txt

/*
Gets all substrings of exactly length n from string

e.g. for all susbtrings of length 5 of the string in A1
=TEXT.SUBSTRINGS(5)(A1)
*/
SUBSTRINGS = LAMBDA(length,
    LAMBDA(string,
        LET(
            substrings,MID(string,SEQUENCE(LEN(string)),length),
            FILTER(substrings,LEN(substrings)=length)
        )
    )
);


/*
Determines if a single character is a letter
*/
IS_LETTER = LAMBDA(char, 
    LET(
        _char,IF(LEN(char)<>1,#VALUE!,char),
        upper,UPPER(_char),
        is_letter,AND(CODE(upper)>64,CODE(upper)<91),
        is_letter
    )
);


/*
Determines if a single character is a number
*/
IS_NUMBER = LAMBDA(char, 
    LET(
        _char,IF(LEN(char)<>1,#VALUE!,char),
        is_number,AND(CODE(_char)>47,CODE(_char)<58),
        is_number
    )
);


/*
Determines if a single character is a parenthesis
*/
IS_PARENTHESIS = LAMBDA(char, 
    LET(
        _char,IF(LEN(char)<>1,#VALUE!,char),
        is_paren,AND(CODE(_char)>39,CODE(_char)<42),
        is_paren
    )
);


/*
Returns TRUE if the item is in the list, FALSE otherwise

E.g. find if the text in A1 contains a semi-colon:
=TEXT.LISTCONTAINS(";")(TEXT.CHARACTERS(A1))

E.g. find if the character in cell A1 is in the dynamic array anchored in cell B1
=TEXT.LISTCONTAINS(A1)(B1#)
*/
LISTCONTAINS = LAMBDA(item,

    LAMBDA(list,
        NOT(ISERROR(XMATCH(item,list)))
    )

);

/*
Determines if a single character is a space

32 - normal space
160 - non-breaking space
*/
IS_SPACE = LAMBDA(char, 
    LET(
        _char,IF(LEN(char)<>1,#VALUE!,char),
        codes,{32,160},
        is_space,TEXT.LISTCONTAINS(CODE(_char))(codes),
        is_space
    )
);

/*
Determines if a single character is a parenthesis

91,93,123,125
*/
IS_BRACKET = LAMBDA(char, 
    LET(
        _char,IF(LEN(char)<>1,#VALUE!,char),
        codes,{91,93,123,125},
        is_bracket,TEXT.LISTCONTAINS(CODE(_char))(codes),
        is_bracket
    )
);


/*
Splits a string into an array of characters
*/
CHARACTERS = LAMBDA(string,
    TEXT.SUBSTRINGS(1)(string)
);

/*
Applies the function to the characters in the string

The function should take a single parameter, to which a character in the string is mapped.

The function determines if that character meets some arbitrary criteria
and returns TRUE if it does, or FALSE otherwise.

So, TEXT.WHERE returns an array of TRUE/FALSE values with ROWS()=LEN(string)
*/
WHERE = LAMBDA(string, function, 
    MAP(TEXT.CHARACTERS(string), function)
);


/*
Determines if a sequence of characters could be a USA zip code

pass an address as the string and list.FIRST, list.LAST, list.ROW(n) or list.JOIN([delimiter]) as the function
*/
ZIPCODES = LAMBDA(string,[function],
    LET(
        substrings,TEXT.SUBSTRINGS(5)(string),
        five_digits,LEN(IFERROR(VALUE(substrings),0))=5,
        zips, FILTER(substrings,five_digits),
        transformed_zips,IF(ISOMITTED(function),zips,function(zips)),
        transformed_zips
    )
);


/*
Removes the characters from the string for which the mapped function returns TRUE

The function should have a single parameter and test that parameter for some condition. 

It should return TRUE if the condition is met, or FALSE otherwise. 

The function is applied to each of the characters in string and those characters
which test TRUE are removed from the result of TEXT.REMOVE

E.g. 
=TEXT.REMOVE("Remove the spaces",TEXT.IS_SPACE)
Returns - "Removethespaces"
*/
REMOVE = LAMBDA(string,function,
    LET(
        chars,text.CHARACTERS(string),
        filtered_chars,FILTER(chars,NOT(text.WHERE(string,function))),
        joined_chars,TEXTJOIN("",FALSE,filtered_chars),
        joined_chars
    )
);


/*
Removes the characters from the string for which the mapped function returns FALSE

Acts as the inverse of TEXT.REMOVE and as such, returns those characters that meet the condition
*/
GET = LAMBDA(string,function,
    LET(
        chars,text.CHARACTERS(string),
        filtered_chars,FILTER(chars,text.WHERE(string,function)),
        joined_chars,TEXTJOIN("",FALSE,filtered_chars),
        joined_chars
    )
);


/*
Trims leading, trailing or both characters from a string
*/
TRIM = LAMBDA(remove, from, [position],
    LET(
        _position, LOWER(IF(ISOMITTED(position),"both",position)),
        _chars_arr, TEXT.CHARACTERS(remove),
        _removal_fn, LAMBDA(x, TEXT.LISTCONTAINS(x)(_chars_arr)),
        _result, IFS(
            _position="both",TEXT.LTRIM(remove,TEXT.RTRIM(remove,from)),
            _position="leading",TEXT.LTRIM(remove,from),
            _position="trailing",TEXT.RTRIM(remove,from),
            TRUE,"Invalid value from parameter 'position'. Must be both, leading, trailing or omitted."
        ),
        _result
    )
);

/*
Trims leading characters from a string
*/
LTRIM = LAMBDA(remove, from,
    LET(
        _from_arr, text.CHARACTERS(from),
        _chars_arr, text.CHARACTERS(remove),
        _reducer, REDUCE(
            from,
            _from_arr, 
            LAMBDA(a, b, 
                LET(
                    //we want to test if the left-most character
                    //is a character to be removed
                    _test_char, LEFT(a,1),

                    // does the left-most character of the current string exist
                    // in the character array? If not, the test is TRUE
                    _is_removal, TEXT.LISTCONTAINS(_test_char)(_chars_arr),

                    //if it exists in the character array
                    //then remove the first character from the current string
                    //otherwise, just put the current string
                    _result, IF(_is_removal, REPLACE(a,1,1,""), a),
                    _result
                )
            )
        ),
        _reducer
    )
);


/*
Trims trailing characters from a string
*/
RTRIM = LAMBDA(remove, from, 
    LET(    

        //reverse the 'from' string so we can use LTRIM to trim the characters
        _reversed, TEXT.REVERSE(from),

        //trim the characters from the left of the reversed string
        _trimmed, TEXT.LTRIM(remove, _reversed),

        //reverse the reversed string
        _result, TEXT.REVERSE(_trimmed),
        _result
    )

);


REVERSE = LAMBDA(t, TEXTJOIN("",TRUE,MID(t,SEQUENCE(len(t),,len(t),-1),1)));