Problems with TEXTJOIN it works but when changed one array it give an intermittent value error

Copper Contributor

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. 

 

 

4 Replies

Hi,

 

I guess it returns #VALUE! for the cells where resulting string exceeds 32767 characters, that is the limit for TEXTJOIN

Hi Sergei,

Thanks for your time, I try to manually reduce the data an still getting the same error. If you see the table submitted you will that the value error seem to occur in the first part where it has to identify the RBS ID to select the REC ID in CPR_OPEN. You can see this happening in the same column with the exact same formula, for one works perfect, but for other get the value error.

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.

 

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.