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.
Related formulas
LAMBDA count words
Strip non-numeric characters
LAMBDA replace characters recursive
Strip numeric characters from cell
Related functions
LAMBDA Function
The Excel LAMBDA function provides a way create custom functions that can be reused throughout a workbook, without VBA or macros.
LET Function
The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write....
MID Function
The Excel MID function extracts a given number of characters from the middle of a supplied text string based on the provided starting location. For example, =MID("apple",2,3) returns "ppl".
MATCH Function
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...
ISNUMBER Function
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.
