Menu
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)
275
In the Data Validation window, in the Settings tab, (1) choose Text length from the Allow drop down, then (2) select less than or equal to from the Data menu, and (3) enter a character limit for Maximum.
Also, each cell in Google Sheets has its data limit. A cell can have no more than 50,000 characters. Note.
Monday, March 14, 2022 We've increased the cell limit in Google Sheets from up to five million cells to up to ten million cells. This limit applies to new, existing, and imported files.
The answer is a BIG NO. Google sheets can handle up to 5 million cells for spreadsheets that are created in or converted to Google Sheets. 40,000 new rows at a time.
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?
188
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
