Exceljet

Quick, clean, and to the point

LAMBDA split text to array

Excel formula: LAMBDA split text to array
Generic formula 
=LAMBDA(text,delim,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(text,delim,"</y><y>")&"</y></x>","//y")))
Summary 

Excel does not provide a dedicated function to split a text string to an array, like PHP's explode(), or Python's split(). However, you can use the LAMBDA function to create a custom function that works 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.

Explanation 

Excel does not provide a dedicated function to split a text string to an array, like PHP's explode(), or Python's split(). However, you can use the LAMBDA function to create a custom function that works in a similar way. Custom LAMBDA functions do not require VBA, but are only available in Excel 365.

The first step in creating a custom LAMBDA function is to verify the logic needed with a Excel 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.

LAMBDA is a beta function available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.