ARRAYFORMULA() does not work with SPLIT()

19,373

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

Solution 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), "/")

Solution 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))))),","))

Solution 4

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.

Share:
19,373
jakub
Author by

jakub

It is useful to keep this snippet here (inspired by some SO user): Welcome to SO! To help us help you, please read about how to make a reproducible example: How to make a great R reproducible example? tl;dr provide data, provide info on packages you use, provide all relevant code (what you have tried so far).

Updated on June 09, 2022

Comments

  • jakub
    jakub almost 2 years

    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.

  • jakub
    jakub over 9 years
    Thanks for the answer. Is there a workaround? If there is and you put it here, I will regard it as an exceptionally great answer! :)
  • jakub
    jakub over 9 years
    I agree with @pnuts - and therefore accept the answer.
  • user1855153
    user1855153 almost 9 years
    I tried your workaround and faced a problem, I posted a new question if you'd like to help me you'd be more than welcome ! stackoverflow.com/questions/30200697/…
  • jakub
    jakub over 8 years
    Given the original wording of the question, I'm still going with @AdamL answer. But this is an excellent alternative! Wouldn't you consider creating a question and then self-answering it? I'm sure a lot of people would find it useful. I upvote at least.
  • Mario
    Mario over 8 years
    @jakub is it allowed to self-answer questions? In any case I'll look out for this question and then refer to this thread if I find one. Thanks for the vote, by the way.
  • AdamL
    AdamL about 7 years
    +myroslav thanks - Google Sheets has changed behaviour of REGEXEXTRACT it seems. New workaround posted.
  • AdamL
    AdamL about 7 years
    And much more importantly, it has been brought to my attention that an array SPLIT actually works now.
  • Aurielle Perlmann
    Aurielle Perlmann about 7 years
    wooohooooo! I didnt know they added it, until just now!! WIN!