Explanation

The 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. However, one disadvantage to recursive LAMBDA functions is that they are not easy to test. This is because they can’t be debugged directly on the worksheet, since a generic (unnamed) LAMBDA does not yet have a name and therefore can’t call itself.

When creating a recursive LAMBDA formula a key consideration is how the formula will “exit” the loop it performs by calling itself. One common approach is to deplete an input parameter each time the formula calls itself, then check if the input is fully depleted before each call, and exit if so. In this way, the input parameter acts like a counter, counting down to zero.

This is the approach taken in this formula – the chars parameter acts like a counter and one character is removed each time the formula calls itself. Before the recursive call, the IF function is used to check if chars is empty. If so, the formula returns the current value for str as a final result and exits. If not, the formula calls itself:

=LAMBDA(str,chars,sub,
  IF(chars="",str, // test and exit if needed
    REPLACECHARS( // recurse
      SUBSTITUTE(str,LEFT(chars),sub),
      MID(chars,2,LEN(chars)-1),
      sub
    )
  )
)

The actual replacement of the characters named in chars with the value of sub is handled by the SUBSTITUTE function here:

SUBSTITUTE(str,LEFT(chars),sub)

SUBSTITUTE can only perform one replacement at a time, which is why this formula is recursive. The parameter str is originally the text string provided to the function for character replacement, but note this value is potentially changing each time the function calls itself. The character to replace is provided by the LEFT function :

LEFT(chars) // get first character of chars

The value to use for replacement comes from sub . The trick to understanding the formula is to see that the result from SUBSTITUTE is used directly to call the ReplaceChars function, and this result becomes the “next” str parameter. In addition, each time ReplaceChars is called, the string chars is depleted by one character with the MID function , and the result from MID becomes the next value for chars used in the call to ReplaceChars.

MID(chars,2,LEN(chars)-1) // remove first character of chars

Once chars has been fully depleted (i.e. chars becomes an empty string), the logical test inside the IF function returns TRUE, and the formula returns the current value of str as a final result.

Extending the formula

Custom LAMBDA functions behave like other functions, so you can easily extend functionality by nesting . For example, you could strip punctuation and replace it with a space character (" “), then clean things up after by nesting ReplaceChars inside of the TRIM function like this:

=TRIM(ReplaceChars(B5,"!@#$%^&*()[]<>-?.,"," "))

The TRIM function will return leading and trailing spaces, and normalize space between words to one space. This will avoid the problem of words being combined when they are separated by punctuation only.

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

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.