New Google spreadsheet Concatenate limit 50000 characters

Question

Migrating to new Google spreadsheets. I have a custom formula that combines a few arrays into one array

=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE('Monthly link'!A10:A&CHAR(13) , 'Monthly link'!R10:R&CHAR(13) , 'Monthly link'!AG10:AG&CHAR(13) , 'Monthly link'!AU10:AU&CHAR(13) )), CHAR(13)))

this formula works perfectly fine in the old Google spreadsheet, but in the new one, it gave me a "Error: Text result of CONCATENATE is longer than the limit of 50000 characters."

Is there a way around this? I've tried the Array_Literal formula but can't seem to get it to work, that seems like the a possible solution. But it seems the function combines arrays across and not down.

I've tried:

=array_literal('Monthly link'!A10:A,'Monthly link'!R10:R,'Monthly link'!AG10:AG,'Monthly link'!AU10:AU)

Answer 1

Don't know if this is working, but might be worth a shot. Someone posted this on Google Docs help forum.

/.../ If you want more than 50,000 characters in a single cell, you can use QUERY's header clause.

Example:

=ArrayFormula(query(row(A1:A70000),,100000))

This creates a cell with 408,893 characters. You can verify by using the LEN function.

Ok I fixed the above line like this, I think it works:

=ArrayFormula(query(A1:A100000,,100000))

This is provided that you have data in column A, from row 1 to row 100000. It will concatenate all of it. I guess max length is 100000 characters?

Answer 2

Looking back to here, you can probably nest concatenate:

=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCAT(CONCATENATE('Monthly link'!A10:A&CHAR(13) , 
'Monthly link'!R10:R&CHAR(13)), CONCATENATE('Monthly link'!AG10:AG&CHAR(13) ,
'Monthly link'!AU10:AU&CHAR(13)) )), CHAR(13)))

I simply added in one more CONCATENATE to combine the strings with only one value, then use CONCAT to combine those.

EDIT

This isn't exactly a fix, but reading google documentation states that you can create an old spreadsheet by going here.

EDIT 2

Try this code:

=TRANSPOSE(SPLIT(CONCAT(ARRAYFORMULA(CONCATENATE('Monthly link'!A10:A&CHAR(13), 
'Monthly link'!R10:R&CHAR(13))), ARRAYFORMULA(CONCATENATE(
'Monthly link'!AG10:AG&CHAR(13), 'Monthly link'!AU10:AU&CHAR(13)))), CHAR(13)))

It seems the error is coming from ARRAYFORMULA, has it has a limit of 50000. Not CONCATENATE or CONCAT. So, I use CONCAT to combine two different ARRAYFORMULAs that both house half of the original data. You can continue to divide these until there are even 4 ARRAYFORMULAs that all only have one dataset if need be.

EDIT 3

Currently, I am working on implementing a function in javascript found here.

You can test it currently by Tools->Script editor->Paste, then to run it go Tools->Script Manager->organizeData->Run.

I'll continue to work on it... it is currently not working, but I am close;)

EDIT 4

I finished it! You can see it here. You need to create a new script using the above instructions (Tools->Script editor->Paste), save it, then you can run it from the Script Editor window or from the spreadsheet by doing Tools->Script Manager->organizeData->Run.

What the script does is gets the data from the forms, puts it in the data to be copied, then it has a strange restriction where it requires a letter in the column to be able to copy it, so it adds a letter in so the script will fill the rows with "undefined". From there, all of the rows have "undefined" in them, so data can be copied to all of them.

If you want to know how to implement the script directly into a cell, you can just put:

=organizeData()

it will call the custom function! See here for more details.

Answer 3

My answer is related to another similar Q, marked as duplicete:

Text result of JOIN is longer than the limit of 50000 characters

My solution is to use the formula:

=query(joinSplit(A2:A, ";"), "select Col1, count(Col1) group by Col1", 0)

where joinSplit(A2:A, ";") is a custom formula.

The code to paste into script editor is:

function joinSplit(column, delim)
{
  var result = [];
  var row = [];
  for (var i = 0, l = column.length; i < l; i++)
  {
    row = column[i].join(delim).split(delim);
    row.forEach( function(elt) { result.push([elt]); } ); 
  }  
  return result;
}

It will return the column of unique items.

If data is:

A;B;C;D
D;D
E;F;A;A
G;A;B;C

The result is column:

A
B
C
D
D
D
E
F
A
A
G
A
B
C

Answer 4

The joinSplit script SAVED MY LIFE.

I tweaked it to trim out whitespace in case your data has a ", " in it.

function joinSplit(column, delim)
{
  var result = [];
  var row = [];
  for (var i = 0, l = column.length; i < l; i++)
  {
    row = column[i].join(delim).split(delim);
    row.forEach( function(elt) { result.push([elt.trim()]); } ); 
  }  
  return result;
}