Explanation

Excel did not originally offer the TEXTSPLIT function. This article describes how to use the LAMBDA function to create a custom function that splits text as a workaround. It’s a good example of how the LAMBDA function can be used to bridge a gap, but the workaround is no longer necessary. I leave the article below for historical reference only. - Dave

The first step in creating a custom LAMBDA function is to verify the logic needed using standard formula. This LAMBDA formula is based on an interesting Excel formula created with the FILTERXML , SUBSTITUTE , and TRANSPOSE functions:

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>","//y"))

In a nutshell, this formula transforms the original text into a primitive XML format, and then parses the XML with the FILTERXML function. Read a detailed description here . Although this formula works, the formula itself is a bit messy and non-intuitive, and therefore a good candidate for a custom LAMBDA function, which will hide the complexity and make the formula easier to use.

Since we already know the formula works , the next step is to convert the formula into a generic (unnamed) LAMBDA formula. We will need two input parameters: one for the text to be split, and one for the delimiter to use when splitting. These need to appear as the first arguments in the LAMBDA formula, followed by a third argument containing the formula to execute, adapted to use the first two arguments by name. The result looks like this:

=LAMBDA(text,delim,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(text,delim,"</y><y>")&"</y></x>","//y")))

This formula can be tested on the worksheet using the LAMBDA testing syntax, which places the input arguments in a separate set of parentheses at the end:

=LAMBDA(text,delim,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(text,delim,"</y><y>")&"</y></x>","//y")))(B5,",")

Finally, we define and name the generic LAMBDA using the Name Manager (see the examples on this page for a more detailed explanation). Once the named formula has been created, it can be used anywhere in the workbook.

Other delimiters

The design of this custom function allows the delimiter to be easily changed to suit the situation:

=SplitTextToArray(A1,",") // split by comma
=SplitTextToArray(A1," ") // split by space (words)
=SplitTextToArray(A1,"-") // split by hyphen

The size resulting array will depend on how many delimiters exist in the original text string.

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.