Excel 365 TEXTJOIN() Bug

Question

Hi Community,

I think I found a bug in Excel. So I used TEXTJOIN() to join stuff and the resultant textstring exceeds 255 characters. The cell (TargetCell) that houses this TEXTJOIN() formula LOOKS error free, and indeed ISERROR(), IFERROR(),ISERR() are not treating the cell as an error when I refer it. 

However, when you select the TargetCell and press F9 to evaluate, it returns a #VALUE! error. Not only this, it also renders COUNTIF() not working properly when being used as a criteria (returning #VALUE! to me).

What I did to jerry-rig it is to use LEFT() to wrap the TEXTJOIN() piece and get the first 255 characters. Works okay for me but I look forward to a fix deployed by Microsoft as this is certainly an inconsistency. 

I am using Office 365 here.

Thanks. Thoughts are welcome.

Answer 1

Hi Horatio,

Thank you for your reply and share your experience in the forum.

As for SUMPRODUCT() formula, it’s the formula: =SUMPRODUCT((D:D=D10)+0). You can find the details in the Official article: SUMPRODUCT function, referring to Example 2. And you can find more information on how the formula works in the article: Use a Different Function.

Best regards,

Cindy

Disclaimer*: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.*

---

* Beware of scammers posting fake support numbers here.

* Kindly Mark answer and Vote Yes this reply if it helps, as it will be beneficial to more community members reading here.

Answer 2

hi Dana, thank you for the reply. No i am not using any kind of name manager to refer cells thus "columnb" does not really have any impact on the calculation. And I did try adding @ sign to the formula, making it 

From

=TEXTJOIN("|",0,UNICODE(MID(B9,SEQUENCE(1,LEN(B9)),1)))  

to 

=TEXTJOIN("|",0,UNICODE(MID(**@B9,SEQUENCE(1,LEN(@**B9)),1)))

still gets error. You can download the file for further details if interested

Answer 3

Hi Cindy,

Thank you for trying and the reply. 

I think you will need to go to the formula bar and highlight+F9 the TEXTJOIN() to evaluate the entire formula then you will get to see that #VALUE! error (though the result DISPLAYS normally but when is used as a criteria in COUNTIF() it does result in #VALUE!).

I tried on different computers with Office 365 and got the same #VALUE! result given the same input. 

Here is the file. Please download it and try it in Excel. (I dont know why Google Sheets is handling it well, no error)

https://docs.google.com/spreadsheets/d/1AQHUzKehMQuHlBMDywgUrgR81XLxtS-S/edit#gid=956831983

The below screenshots will help provide more information

Best,

Horatio

Answer 4

Hi Horatio,

Thank you for your post in the forum.

Based on your description, I try to test at our end, however, there isn’t any error of “#VALUE!”. The screenshot below is shown my test content. In my test, exceeding 255 characters won’t cause some error. If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! Error, more details, please refer to the TEXTJOIN function: Remarks. 

Could you please provide a sample file with an example indicating the example so that I can provide more efficient help to you, and test it at our end.

Besides, could you please provide the product information screenshot like below?

Best regards,

Cindy