ARRAYFORMULA() does not work with SPLIT()

Question

Why doesn't the split formula get expanded over the entire column when I use =arrayformula(split(input!G2:G, ",")) ?

I get result only for the input!G2 cell, but not the rest in the G column. Other formulas like =arrayformula(find(",", input!G2:G)) seem to function and get expanded without problems.

Answer 1

SPLIT applied to a vertical array does, now, work. (Jan 4 2017)

=ArrayFormula(SPLIT(input!G2:G,","))

or for error masking

=ArrayFormula(IFERROR(SPLIT(input!G2:G,",")))

Note: the resultant array is as wide as the maximum number of SPLITted elements.

---

(The remainder of the answer no longer applies, but retained for... historical purposes?)

This will be regarded as a really bad answer, but: it just doesn't.

I suspect it might be because it potentially will produce a jagged array (different number of elements in each row), which may be considered problematic. However, an "array SPLIT" used to "work" in the previous version of Sheets (albeit with a bug where it didn't work properly on the first element of the array).

SPLIT is not alone as a function that can't be iterated over an array (eg INDIRECT, INDEX, SUMIFS).

---

A workaround (edited Jan 4 2017):

=ArrayFormula(REGEXREPLACE(input!G2:G&REPT(",",6),REPT("([^,]*),",6)&",*","$"&COLUMN(OFFSET(A1,,,1,6))))

The 6 in the OFFSET(A1,,,1,6) and the REPT functions determines the maximum number of elements in the SPLIT. You could inject an expression that would calculate the maximum number of elements from the column of data, but then performance would suffer even more.

Also, a caveat here is that it will only support splitting by a single character (in this case a comma).

Alternatively, you could look at a Google Apps Script custom function.

---

This previous workaround no longer works, as REGEXEXTRACT no longer appears to support an array for the second argument - as it stands in January 2017, anyway.

=ArrayFormula(IFERROR(REGEXEXTRACT(","&input!G2:G,"^"&REPT(",+[^,]+",COLUMN(OFFSET(A1,,,1,6))-1)&",+([^,]+)")))

Answer 2

You can use this script:

function better_split(col, separator) {
  var new_col = [];
  for (var r = 0; r < col.length; r++) {
    if (col[r]) {
      new_col.push(col[r][0].split(separator));
    }
  }
  return new_col;
}

use it like so (for performance reasons):

=better_split(filter(A2:A, len(A2:A)>0), "/")

Answer 3

Another workaround would be to first JOIN your G2:G column with commas using ARRAYFORMULA. Then you can SPLIT it. Finally you can optionally TRANSPOSE it back into a column (otherwise it will output as a row).

=TRANSPOSE(SPLIT(ARRAYFORMULA(JOIN(",",FILTER(G2:G,NOT(ISBLANK(G2:G))))),","))

Answer 4

An array formula is not required since Data > Split text into columns... works without any function or formula.

Answer 5

It can work, but you have to pad and constrain to make it a square result.

Take this data:

           ColA             COLB
    Row1   Data             ={"Head1","Head2","Head3","Head4","Head5","Head6","Head7";ARRAY_CONSTRAIN(ARRAYFORMULA(SPLIT(A$2:A&" , , , , , , ",",",FALSE,FALSE)),COUNTA(A2:A),7)}
    Row2   1,2,3,4,5,6,7,a
    Row3   1,2,3,5

Gives you this:

           ColA                   COLB
    Row1   Data             Head1 Head2 Head3 Head4 Head5 Head6 Head7 
    Row2   1,2,3,4,5,6,7,a  1     2     3     4     5     6     7
    Row3   1,2,3,5          1     2     3     4     5

This is formula:

={"Head1","Head2","Head3","Head4","Head5","Head6","Head7";ARRAY_CONSTRAIN(ARRAYFORMULA(SPLIT(A$2:A&" , , , , , , ",",",FALSE,FALSE)),COUNTA(A2:A),10)}

First we constrain the result to the maximum acceptable results using ARRAY_CONSTRAIN. You can calculate the max value or hardcode it. I used 7 cols and as many rows as counted. ARRAYFORMULA used to run it down. SPLIT text value is padded with empty values to ensure it exceeds the constrain value. This allows accommodation of values in ColA of varying length. FALSE I think will avoid them being trimmed and upsetting the array. Might not be necessary, I didn't test.