Mar 13 2019 07:35 PM
Hi!
I've been trying textjoin and encounter that when I copy the formula and change the array for text it will give a value error in some cases.
I use the following formula and works perfectly:
=TEXTJOIN(CHAR(10),TRUE,IF(CPR_OPEN!$H$2:$H$5050=$A5,IF(CPR_OPEN!$Q$2:$Q$5050="GSM",IF(CPR_OPEN!$R$2:$R$5050<>"CLOSED",CONCATENATE(CPR_OPEN!$A$2:$A$5050," ",CPR_OPEN!$K$2:$K$5050),""),""),""))
But when I change the last array from k2:k5050 to f2:f5050 then began to value error but in some cells not in all. The basic formula are in columns J thru M, and on N I changed the array. But keeping the formula basic you can see in U5 and V5 the error and in U6 and V6 no error.
Mar 14 2019 07:19 AM
Hi,
I guess it returns #VALUE! for the cells where resulting string exceeds 32767 characters, that is the limit for TEXTJOIN
Mar 14 2019 08:53 AM
Mar 14 2019 09:30 AM
I did the simple test
in V6 of Universe is the formula
=TEXTJOIN(CHAR(10),TRUE,CONCATENATE(CPR_OPEN!$A$2:$A$19," ",CPR_OPEN!$BA$2:$BA$19))
which returns an error. If you change 19 on 18 it returns some result.
In column AY of CPR_OPEN you may see the cumulative length of texts in column BA.
For row 18 it's 30319, for the next one - 49191. We have the result for first one and an error for the next one.
Mar 14 2019 12:43 PM
Thanks again tor the info, how I might be able to limit the amount of characters it can recover from each option? As you can see in Universe columns N and O Textjoin will bring a lot of info per option, maybe limit it to 5K's per option in case have more than 4 options.