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:
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:
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:
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.
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.