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?