2

I am trying to combine cells and show in one cell as each cell contains product skus comma seperated. Need to combine these cells with comma seperator in seperate cell in same column. For this i am using

=TEXTJOIN(",",TRUE, G5,G10,G19,G27,G39,G46,G59)

But getting error:

Text result of TEXTJOIN is longer than the limit of 50000 characters.

player0
  • 124,011
  • 12
  • 67
  • 124
kiran kumar
  • 33
  • 1
  • 11
  • Your goal is unclear. Unless each of the seven cells being joined had 7,000 characters, you wouldn't reach 50,000 characters. And having 50,000 characters in one cell isn't even visible on a screen. I suggest sharing a link to your spreadsheet and *showing* what you are trying to do, rather than just *telling* us and showing a formula that isn't working. – Erik Tyler May 20 '22 at 07:38
  • i have skus like abc,acb,adc,dhv,jsc.....etc like this different skus in each cell example in G5 and G8 I need to combine and show in G4 as G5,G8 – kiran kumar May 20 '22 at 07:55
  • =ARRAYFORMULA(QUERY(G5:G,,10000000)) This working for me without limit error, but just need a delimiter in between G5 and G8 like G5skus(,)G8skus and also dont need range like G5:G just a selected cells G5, G7, G9 like this – kiran kumar May 20 '22 at 07:57
  • I or others may be able to offer you a full solution exactly as you'd like. But without being able to actually see the spreadsheet alongside an exact example of the commas and spacing you want, it would not be an efficient use of the volunteers' time here to guess. – Erik Tyler May 20 '22 at 22:12

3 Answers3

3

use query (that's the only way):

=QUERY({G5;G10;G19;G27;G39;G46;G59}&",";;9^9)

or:

=QUERY({QUERY({G5;G10;G19;G27;G39;G46}&",";;9^9); G59};;9^9)
player0
  • 124,011
  • 12
  • 67
  • 124
0

A Sheets cell cannot have more than 50,000 characters:

When you convert a document from Excel to Google Sheets, any cell with more than 50,000 characters will be removed in Sheets.

I'd suggest you to split your data into several cells.

Reference:

Iamblichus
  • 18,540
  • 2
  • 11
  • 27
0

According to @player0's answer and his comment about "query adds one empty space between each cell", you can use the ARRAYFORMULA, SPLIT, and SUBSTITUTE functions to manipulate the output of the QUERY function.

=ARRAYFORMULA(SPLIT(SUBSTITUTE(QUERY({G5;G10;G19;G27;G39;G46;G59}&"@@@@@",,9^9),"@@@@@ ",""),"@@@@@"))

By setting a unique possible (with lower chance to exists in your data) character, such as "@@@@@", and adding the space after it (e.g., "@@@@@ "), you can substitute for an empty value (e.g., ""). However, in the end, it will keep one "@@@@@" without space. To solve this, you can split it and get the first part only.

This formula will also achieve a SUBSTITUTE Limitation, however, it can sustain a little more than 50000 characters.

André Barros
  • 1
  • 1