Completely stuck with TEXTJOIN #VALUE error

[pre0rm]

I'm trying to join several cells containing comma separated values in a table. I use an array formula as below;

={TEXTJOIN(",",TRUE,IF([@[Part Number]]=Table1[Part Number],Table1[Fitment],""))}

The formula works perfectly if all cells contain 255 characters or less. If a cell contains more than this, it results in the #VALUE error. This error does not occur if I try to use TEXTJOIN on the cells without the array;

ie.
=TEXTJOIN(",",TRUE,Table1[Fitment])

This makes me think that it's a limitation of array formulas and the text length in the cell. I can't see any mention of it on Excel's help pages.

Am I right that the array formula limit is the problem. Any ideas for a workaround?

 

[ChrisFoster]

To answer the first part of the question, yes there is a 255 character limit. I'm sure someone will be along soon though with an alternative for you.

 

[OEDA]

Kept tripping over this page while looking for a solution, and figured it should share what I found

To answer the first part of the question, yes there is a 255 character limit. I'm sure someone will be along soon though with an alternative for you.

Maybe not so 'soon' but I kept getting directed to this URL in search results while myself trying to solve the same problem. I expect the OP has long since solved their problem but I'm going to close the circle in case another soul comes along in the future.

Using the FILTER approach avoids the 255 character limitation of the IF approach.

{=TEXTJOIN(",",TRUE,FILTER(Table1[Fitment],Table1[Part Number]=[@[Part Number]]))}

You will see a #CALC! error if there are 0 ()zero) matches. If 0 matches are a possibility in your use case, you can add error handling to return nothing "" (or "something" if you prefer):

{=IFERROR(TEXTJOIN(",",TRUE,FILTER(Table1[Fitment],Table1[Part Number]=[@[Part Number]])),"")}
{=IFERROR(TEXTJOIN(",",TRUE,FILTER(Table1[Fitment],Table1[Part Number]=[@[Part Number]])),"something")}

 

[Fluff]

Hi & welcome to MrExcel.
A couple of things,
1) You do not need to enter that formula as an array formula when using 365.
2) you don't need to use Iferror, as Filter has it's only error handling, so you can use

=TEXTJOIN(",",TRUE,FILTER([Fitment],[Part Number]=[@[Part Number]],""))

 

[OEDA]

Hi & welcome to MrExcel.
A couple of things,
1) You do not use to enter that formula as an array formula when using 365.
2) you don't need to use Iferror, as Filter has it's only error handling, so you can use

=TEXTJOIN(",",TRUE,FILTER([Fitment],[Part Number]=[@[Part Number]],""))

Good points both.

 

[Fluff]

You're welcome.

 

[Peter_SSs]

We don't know how long the OP's cell texts actually were or how many rows were in Table1, but even though the FILTER method avoids the initial error problem, with long texts and a reasonable number of rows in Table1, there could still be an error since TEXTJOIN has its own character limit (32,767 characters)

 

[OEDA]

So to summarize for the OP from 2019 or the next person that has the same problem:

1. Using FILTER avoids the #VALUE error from the 255 character limitation of the IF approach

=TEXTJOIN(",",TRUE,FILTER([Fitment],[Part Number]=[@[Part Number]]))​

​

2. If empty results are expected, the 'if_empty' option in FILTER avoids #CALC! errors.

=TEXTJOIN(",",TRUE,FILTER([Fitment],[Part Number]=[@[Part Number]],""))​

​

3. If formula result exceeds 32,767 characters TEXTJOIN will return a #VALUE! error which can be handled...

a) ...by using IFERROR to replace the error message (I chose a blank result).​

=IFERROR(TEXTJOIN(",",TRUE,FILTER([Fitment],[Part Number]=[@[Part Number]],"")),"")​

​

b) ...by avoiding triggering an error by first testing the total cumulative length of all strings the OP intends to TEXTJOIN. Using this last approach with an IF statement will result in a much longer formula but may be useful to know related to the current discussion.​

​

To count the number of characters even if they exceed 32,767:​

​

=SUMPRODUCT(LEN(FILTER([Fitment],LEN([Part Number])=[@[Part Number]],"")))​

@Peter_SSs feedback got me thinking about ways to perform the TEXTJOIN but split it into as many 32,767 character cells as necessary for the result without generating any errors...

 

[Peter_SSs]

To count the number of characters even if they exceed 32,767:

=SUMPRODUCT(LEN(FILTER([Fitment],LEN([Part Number])=[@[Part Number]],"")))

Might be worth checking that formula.

 

[OEDA]

Good catch. There's a typo in it.