Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ARRAYFORMULA() does not work with SPLIT()

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.

like image 827
jakub Avatar asked Oct 01 '14 08:10

jakub


People also ask

How do you use Arrayformula?

To use it in Google Sheets, you can either directly type “ARRAYFORMULA” or hit a Ctrl+Shift+Enter shortcut (Cmd + Shift + Enter on a Mac), while your cursor is in the formula bar to make a formula an array formula (Google Sheets will automatically add ARRAYFORMULA to the start of the formula).

How do I split comma separated values into rows in Google Sheets?

Select the text or column, then click the Data menu and select Split text to columns... Google Sheets will open a small menu beside your text where you can select to split by comma, space, semicolon, period, or custom character. Select the delimiter your text uses, and Google Sheets will automatically split your text.


1 Answers

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)&",+([^,]+)")))

like image 92
AdamL Avatar answered Sep 18 '22 19:09

AdamL