Forum Discussion
Array formula returning #VALUE when using large text-based field
The formula could be
=TEXTJOIN(CHAR(10),TRUE,LEFT(AdhocTable[Immigration Status Update.Comments],IF([@[Assignment ID]]=AdhocTable[Assignment ID],LEN(AdhocTable[Immigration Status Update.Comments]),0)))
entered as array one (Ctrl+Shift+Enter). I take CHAR(10) (new line) as separator, you may change on any other symbol or text.
Please check attached.
joerames , great finding, I didn't know about that. Yes, it looks like conditional functions as IF() return max 255 characters length text as an array element.
I played with simple model
the workaround could be
=TEXTJOIN("--",TRUE,LEFT($C$3:$C$5,IF($D$3:$D$5="a",LEN($C$3:$C$5),0)))
(CSE formula).
TEXTJOIN itself returns #VALUE if the resulting string is more than 32K length, don't remember exactly.
SergeiBaklan Sorry for the slow reply. I haven't quite figured out how to use your advice. I've attached a sample file. I messed with it quite a bit. Not an expert here.
The file has 3 cases. One is working. One doesn't error, but returns 0's and I don't want that. The final case shows the long field and it #ing out.
Would love for you to take a peak and help me along!
The formula could be
=TEXTJOIN(CHAR(10),TRUE,LEFT(AdhocTable[Immigration Status Update.Comments],IF([@[Assignment ID]]=AdhocTable[Assignment ID],LEN(AdhocTable[Immigration Status Update.Comments]),0)))
entered as array one (Ctrl+Shift+Enter). I take CHAR(10) (new line) as separator, you may change on any other symbol or text.
Please check attached.
SergeiBaklan Brilliant!
