Using ArrayFormula with a Dynamic Number of Column Header Names

Question

My goal is to use ArrayFormula with the SPLIT() function, and name the headers of each column.

My problem is that the formula below only works when the number of headers declared exactly matches the first row's number of elements to split ie. if there are 3 elements being split on the first row, the formula needs 3 headers named (g1, g2, g3), but if any rows have more than 3 elements to split, it gives an error.

Is there a way to make the column header names dynamic in number, so that the number of elements to split can be, say, from 0-10? The elements to be split will always be separated by a comma and no spaces.

=ArrayFormula({"g1", "g2", "g3";if(A2:A="","",split(A2:A,","))})

link to example: https://docs.google.com/spreadsheets/d/1c2pskSYsGs12Yjbn-5gORQ22mDSaC9cSnp1nWeULlf4/edit?usp=sharing

Answer 1

You can achieve it by combining the index function, the sequence function and the max function. Here is the thought process behind it:

• The max function (you can read more about it here) will retrieve the maximum value of the orders column.
• The sequence function (you can read more about it here) will generate a series starting at 1 and ending at the previous maximum value.
• The index function (you can read more about it here) will distribute the elements of the sequence (with a "g" in front) across as many cells as elements are in the sequence.

If you combine those, you get:

=INDEX("g"&SEQUENCE(1,MAX(B:B)))

Answer 2

You can try:

=index(iferror({"g"&sequence(1,max(len(substitute(
transpose(query(transpose(if(iferror(split(A2:A,","))="",,"z")),,9^9)),
" ",))));split(A2:A,",")}))

If we can use the Orders column, it's as simple as:

=index(iferror({"g"&sequence(1,max(B:B));split(A2:A,",")}))