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)

Jonny · Answer 1 · 2015-05-07T05:17:29.540

11

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?

edited May 07 '15 at 05:17

answered May 07 '15 at 05:10

Jonny

15,955
18
111
232

1

Thanks, this trick worked like a charm for me. I've tried to test the length limit and it worked for 4 035 566 characters. Might be more as I stopped testing at that point. – Artur Hebda Feb 28 '19 at 09:19

score 9 · Accepted Answer · edited May 23 '17 at 12:17

9

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.

edited May 23 '17 at 12:17

Community

1
1

answered Mar 31 '14 at 13:19

Liam McInroy

4,339
5
32
53

I tried it and unfortunately it didn't work. `Error: Text result of CONCAT is longer than the limit of 50000 characters.` – jason Mar 31 '14 at 13:25
@jason_can't_code Hmmmm... Can you use two different cells? – Liam McInroy Mar 31 '14 at 13:29
can you elaborate? I guess i could, but it wouldn't be dynamic. How would I know where to start the second cell? – jason Mar 31 '14 at 13:32
@jason_can't_code Good point... I'm going to check the documentation on these functions... I think one might automatically insert newlines so you then effectively increase your char count by a lot – Liam McInroy Mar 31 '14 at 13:34
Yeah, this function basically a work around for joining many databases into one since there is no `JOIN()` function in the `QUERY` function. It's ghetto but it works. – jason Mar 31 '14 at 13:37
@jason_cant_code Unfortunately, I cannot test the new google sheets... However, check my edits as you can still create old sheets, and also how many data values total are you using? – Liam McInroy Mar 31 '14 at 22:40
@jason_cant_code I think I have a solution. Check edits – Liam McInroy Mar 31 '14 at 22:49
I have just over 50000 characters i think because it used to work. Then it stoppped. That means it grew to be over 50k. Btw, edit 2 had some errors `Error: Wrong number of arguments to SPLIT. Expected between 2 and 3 arguments, but got 1 arguments.` I can't go back to the old spreadsheets anymore. Too slow. – jason Apr 01 '14 at 00:06
`Error: Text result of CONCAT is longer than the limit of 50000 characters.` I was hopeful, because spreadsheet computed for quite a while (like 10 seconds), but at the end... still same problem. – jason Apr 01 '14 at 01:25
@jason_cant_code Check my edits... Ill continue working an update it later – Liam McInroy Apr 01 '14 at 03:02
ok cool. I'm going to give you the bounty regardless. Thanks for the effort! – jason Apr 01 '14 at 03:23
@jason_cant_code I've solved it! Check my edits for all the info – Liam McInroy Apr 01 '14 at 22:51
wow. +200 lines of code? going to take a while for me to absorb it, but the bounty is yours. – jason Apr 02 '14 at 00:21
@jason_cant_code Haha... Most of it is google's, from [here](https://developers.google.com/apps-script/guides/sheets)... The only part you really need to worry about is the `organizeData()` function. – Liam McInroy Apr 02 '14 at 00:45

score 1 · Answer 3 · answered Jan 09 '18 at 08:09

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

score 0 · Answer 4 · answered Jun 26 '21 at 15:50

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

New Google spreadsheet Concatenate limit 50000 characters

4 Answers4

Linked

Related