Explanation

This is an experimental formula to strip characters from text. The experimental part is using character codes instead of regular characters as a way to make the formula case-sensitive, and providing a way to reverse the logic of the formula with the “keep” input parameter. Unlike the formula explained here , this formula is not recursive.

The formula takes four inputs:

text - the incoming text chars - the characters to strip rep - the character to replace stripped characters with keep - strip or preserve chars (FALSE = strip, TRUE = preserve)

The keep parameter is a boolean that “flips” the behavior of the function from stripping characters to preserving characters.

In a nutshell, the SEQUENCE function is used to generate two arrays : one for the text , one for chars . In order to make the function case-sensitive, these arrays are composed not of characters but of the ASCII codes for each character.

textarr,CODE(MID(text,SEQUENCE(LEN(text)),1)),
chararr,CODE(MID(chars,SEQUENCE(LEN(chars)),1))

The MID function extracts one character at a time, and CODE returns the numeric code. Inside the IF function , the logical test is:

ABS(keep-ISNUMBER(MATCH(textarr,chararr,0))) // logical test

The ISNUMBER and MATCH combo checks each code in textarr against codes in chararr . The ABS function is used as a way to reverse the logic of the formula. If keep is set to FALSE or zero, the ISNUMBER+MATCH logic is unchanged and chars are stripped. If keep is TRUE (or 1) the logic is reversed – chars are preserved and other characters are stripped.

LAMBDA version

To convert the formula to the LAMBDA function so that the formula can be named and reused throughout the workbook, the four input arguments are moved to the front, followed by the calculation, wrapped in LET:

=LAMBDA(text,chars,rep,keep,
LET(
textarr,CODE(MID(text,SEQUENCE(LEN(text)),1)),
chararr,CODE(MID(chars,SEQUENCE(LEN(chars)),1)),
TEXTJOIN("",1,IF(ABS(keep-ISNUMBER(MATCH(textarr,chararr,0))),rep&"",CHAR(textarr)))
))

After the LAMBDA function is named “StripCharacters” the function can be called like this:

=StripCharacters(B5,"()-","",FALSE)

Per the example shown, the text is in cell B5, the characters to strip are “()-”, the replacement string is “”, and preserve mode is set to FALSE.

Explanation

LAMBDA function can be used to create custom, reusable functions in Excel. This example illustrates a feature called recursion, in which a function calls itself. Recursion can be used to create elegant, compact, non-redundant code. This example is primarily proof of concept, to show a very simple recursive LAMBDA function.

When creating a recursive LAMBDA formula a key consideration is how the formula will “exit” the loop it performs by calling itself. In this example, before the recursive call happens, the IF function is used to check the last character of the input text string ( str ). If the last character is not equal to the target character ( char ) the function exits and returns the current value of str . Otherwise, the formula calls itself:

=LAMBDA(str,char,
  IF(RIGHT(str)<>char,str, // test and exit if needed
    StripTrailingChars( // recurse
      MID(str,1,LEN(str)-1),
      char
    )
  )
)

The actual removal of the trailing character is handled by the MID function and LEN function :

MID(str,1,LEN(str)-1)

The MID function returns the value of str without the last character. MID strips one character at a time, which is why this formula is recursive. The result is given to the StripTrailingChars function, along with the original value for char . When all the target trailing characters have been removed, the function returns the current value for str .

You can find more general information about the LAMBDA function here .