Exceljet

Quick, clean, and to the point

Array of arrays

Example of array of arrays limitation in Excel formulas

"Array of arrays" is a name for a limitation of Excel's calculation engine that can affect certain formulas that return multiple results. Excel's current formula engine will not return an "array of arrays" and the result may be a truncated version of the data (as in the example) or an error in some cases.

"Array of arrays" is not related to the TEXTSPLIT function per se, but TEXTSPLIT is an easy way to see the limitation in action. When TEXTSPLIT is used on a single cell, it returns the text split by delimiter in a single array whose values spill onto the worksheet into multiple cells. However, when you call TEXTSPLIT on a range of cells, TEXTSPLIT returns an array of results where each result is itself an array, creating an "array of arrays". As seen in the example shown, the result is that only the first name is returned in each row.