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. 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.
Other delimiters
The design of this custom function allows the delimiter to be easily changed to suit the situation: The size resulting array will depend on how many delimiters exist in the original text string.
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.
