Summary

When dynamic arrays were first introduced, Excel did not offer a function to split a text string into an array, like PHP's explode(), or Python's split(). However, with the LAMBDA function it was possible to create a custom function that worked the same way. In the example below, the formula in cell C5 is: 

=SplitTextToArray(B5,",")

This formula splits the text in B5 using a comma (",") as the delimiter. The result is a horizontal array that spills into columns D through H. The delimiter is provided as the second argument to the function, and can be changed to suit the situation.

Note: This is an interesting and useful LAMBDA example, but Excel 365 now provides the TEXTSPLIT function, which makes this workaround unnecessary in newer versions of Excel.

Generic formula

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

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.

Related formulas

Excel formula: Split comma-separated values to multiple columns

Split comma-separated values to multiple columns

In this example, the goal is to split comma-separated values (CSV) in column B into multiple columns, as seen in the worksheet shown. Each text string in column B contains 5 fields separated by commas, so we expect to get 5 columns of data as a result. The header row in column D is manually entered...
Excel formula: LAMBDA replace characters recursive

LAMBDA replace characters recursive

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...
Excel formula: LAMBDA count words

LAMBDA count words

The LAMBDA function can be used to create reusable, custom functions in Excel without VBA or macros. The first step in creating a LAMBDA function is to verify the formula logic needed in a standard Excel formula. In this example, the base formula is: =LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(...
Excel formula: LAMBDA contains one of many

LAMBDA contains one of many

Excel does not provide a dedicated "contains" function, but you can create a custom function to test if a cell contains one or many strings with the LAMBDA function . LAMBDA functions do not require VBA, but are only available in Excel 365 . The first step in creating a custom LAMBDA function is to...

Related functions

Excel LAMBDA function

LAMBDA Function

The Excel LAMBDA function provides a way to create custom functions that can be reused throughout a workbook, without VBA or macros.
Excel FILTERXML function

FILTERXML Function

The Excel FILTERXML function returns specific data from XML text using the specified XPath expression.
Excel TRANSPOSE function

TRANSPOSE Function

The Excel TRANSPOSE function "flips" the orientation of a given range or array: TRANSPOSE flips a vertical range to a horizontal range, and flips a horizontal range to a vertical range.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions