The new Google Concatenation table limits 50,000 characters

Question

Transition to new Google spreadsheets. I have a custom formula that combines multiple 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 fine in the old Google spreadsheet, but in the new one it gave me " Error: Text result of CONCATENATE is longer than the limit of 50000 characters."

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

I tried:

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

Answer 1

Looking back at here , you can possibly combine concatenation:

=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)))

CONCATENATE, , CONCAT .

, Google , , .

2

:

=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)))

, ARRAYFORMULA, 50000. CONCATENATE CONCAT. , CONCAT ARRAYFORMULA, . , 4 ARRAYFORMULA, .

3

javascript, .

Tools->Script editor->Paste, Tools->Script Manager->organizeData->Run.

... , ;)

EDIT 4

! . script, (Tools->Script editor->Paste), , script , Tools->Script Manager->organizeData->Run.

script - , , , , , script "undefined". "undefined", .

, script , :

=organizeData()

! . .

Answer 2

, , . - Google.

/.../ If you want more than 50,000 characters in a single cell, you can use QUERY 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.

, , , :

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

, A, 1 100000. . , 100000 ?

Answer 3

Q, duplicete:

JOIN 50000

:

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

joinSplit(A2:A, ";") - .

script:

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;
}

.

:

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

:

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