Created attachment 124781 [details] File with TEXTJOIN entered as array formula TEXTJOIN(delimiter,skip empty cells,text) returns an array of cell when "text" range is defined with a formula. Example : =TEXTJOIN(", ",1,INDEX(A1:A10,{1;3;5})) returns "A1, A3, A5" (means content of A1,A3 and A5) three times instead of one. See attachment for a more detailled case.
Created attachment 124785 [details] Excel Online display I Have no new master installed atm (as it seems) but this is a screenshot how it looks when displaying it in Excel Online. Hope that helps.
The TEXTJOIN examples in attachment 124781 [details] (File with TEXTJOIN entered as array formula) and attachment 124785 [details] (Excel Online display) are - as the attachment says - entered as array formula. That means that the result will be an array. Calc produces the joined test strings and puts the result in 3 cells to create that array. Excel doesn't join the separate strings, disregards the delimiter and puts the input strings in 3 cells to create that array. IMHO this is a case which use is not supported by TEXTJOIN. But Excel nor Calc produce an error message. And that presents a problem: -the output from Excel is not correct, the result is not a concatenated text string with delimiters between the input strings; -the output from Calc differs from Excel. Which is 'better' is not important, interoperability is. However, it is a doubtful action to make Calc to provide an incorrect result because Excel does. If my reasoning is sound, one could state that Excel has bug (either not supporting array output or not producing a proper error output) and Calc has to follow as TEXTJOIN in Calc is there because it is in Excel. Should TEXTJOIN be added to the OpenFormula standard, the situation changes, Then Calc can comply with that standard. To make a long story short, I hesitate to confirm this bug report and don't have a solution other than waiting for definite information about the unexpected and AFAIK undocumented behaviour of TEXTJOIN in Excel.
As I said before, I don't have Excel and so can't do more test. Excel show an array of 3 rows because Calc have store this formula like this. Wrong result in Excel were expected. When INDEX have a array as row or column argument, the result of INDEX is not handled properly in another function in array formula in Excel. That's a known bug. A way to force Excel to recognize the result of INDEX as an array is to force the recognition with N (or T in case of text) and IF. Dennis, can you try entering this formula in a cell: =TEXTJOIN(", ",1,INDEX(B:B,N(IF(1,MODE.MULT(IF(A4:A12=D4,{1,1}*ROW(A4:A12))))))) instead of =TEXTJOIN(", ",1,INDEX(B:B,MODE.MULT(IF(A4:A12=D4,{1,1}*ROW(A4:A12))))) in Excel and save the file as .xlsx
(In reply to GerardF from comment #3) > Dennis, can you try entering this formula in a cell: > =TEXTJOIN(", > ",1,INDEX(B:B,N(IF(1,MODE.MULT(IF(A4:A12=D4,{1,1}*ROW(A4:A12))))))) > instead of > =TEXTJOIN(", ",1,INDEX(B:B,MODE.MULT(IF(A4:A12=D4,{1,1}*ROW(A4:A12))))) > > in Excel and save the file as .xlsx This formula was to make the same job, but you can test only a simple formula like : =TEXTJOIN(",",1,ROW(A1:A3))
1) Excel Online is crap. I really do not understand how that web app should replace the "fat client" (as some managers and CEOs think) 2) Excel is crap. I do want to switch to English function names! This is such a big plus in LibreOffice Calc! 3) You cannot "convert" an existing ODS to an XLSX file. You can save the open file under another name (save a copy) but not to save it under a different file format. But always a big yellow fat warning about incompatibilities... -.- 4) Warning that the formula is incorrect. Well ok, but WHAT PART of the formula? Errr. Crap. 5) I will retry that tomorrow.
(In reply to Dennis Roczek from comment #5) > 1) Excel Online is crap. I really do not understand how that web app should > replace the "fat client" (as some managers and CEOs think) > > 2) Excel is crap. I do want to switch to English function names! This is > such a big plus in LibreOffice Calc! > > 3) You cannot "convert" an existing ODS to an XLSX file. You can save the > open file under another name (save a copy) but not to save it under a > different file format. But always a big yellow fat warning about > incompatibilities... -.- > > 4) Warning that the formula is incorrect. Well ok, but WHAT PART of the > formula? Errr. Crap. > > 5) I will retry that tomorrow. Could you offer your findings with Excel to Microsoft? This bug list only handles LibreOffice bugs/enhancements ;-) Looking forward to your results with Excel and this use case of TEXTJOIN. As soon as I know how Excel handles this I can fix Calc's TEXTJOIN if needed.
(In reply to Winfried Donkers from comment #2) > The TEXTJOIN examples in attachment 124781 [details] (File with TEXTJOIN > entered as array formula) and attachment 124785 [details] (Excel Online > display) are - as the attachment says - entered as array formula. That means > that the result will be an array. > ... This is simply wrong. Only the evaluated parameter(s) have to be passed to the functions as arrays. Compare with accumulating numeric functions like PROD or IMSUM (actually a kind of text-function). The functions will then have to convert these arrays into sequences (See subchapter 4.11.12 of the mentioned document). Of course, a function body may omit this and treat these sequences as arrays for the sake of efficiency, if the results to obtain are not distorted. Please read subchapter 3.3 of http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.odt . For the data-parameters (all those of CONCAT and starting with the second one for TEXTJOIN) we need a data type of "TextSequenceList" which is not yet specified, but may be assumed in analogy to "NumberSequenceList" or "ComplexSequenceList". The examples attached to bug#101137, in specific the reworked newer one should be taken in account. If you think I should report the errors demonstrated and explained there using orange background for highlighting as independent bugs anew, please tell me. Given a few days I may also find the time to suggest a complete extension to the mentioned specification with respect to this subject. I may also sketch an implementation for CONCAT and TEXTJOIN in pseudocode or in BASIC. (I cannot study and train in my late days everything needed to contribute to the original code myself.)
So, can someone with access to the relevant Excel version please come up with a description of what Excel exactly does when entering CONCAT and TEXTJOIN as array formulas? Pointers to the ODF OpenFormula definition don't help here, CONCAT and TEXTJOIN are not covered there.
*** Bug 101134 has been marked as a duplicate of this bug. ***
Bug 101134 has some additional attachments that might be of interest: https://bugs.documentfoundation.org/attachment.cgi?id=126416 https://bugs.documentfoundation.org/attachment.cgi?id=126823
(Just a silly workaround.) Meanwhile you will get the expected scalar results with CONCAT and TEXTJOIN entered for evaluation in array mode using formulae like {=INDEX(CONCAT(A1:A10&B1:B10);1} or {=INDEX(TEXTJOIN(", ";TRUE();A1:A10&B1:B10);1)} This is not avoiding bad results due to bugs concerning the array-evaluation of the concatenation operator & (e.g. A1:A10&B1:B10) as discussed in bug#101137.
No harm meant! (In reply to Eike Rathke from comment #8) > ... Pointers to the ODF OpenFormula definition > don't help here, CONCAT and TEXTJOIN are not covered there. Pointers to the OpenFormula document cannot help with a specification for CONCAT and TEXTJOIN. However, they can help to understand that the actual behaviour under array-evaluation is improper with respect to general mandatory requirements (subchapter 3.3). This would apply even if Excel 2016 did it otherwise - and also was not based on any specification of the new functions in advance. In addition: If Excel 2016 actually returns an array of identical elements under the mentioned conditions, It will most likely also be in disorder concerning general principles applying to Excel itself. Thus MS will have to fix a bug themselves in that case. Is somebody capable of checking for my above claim referring to the (should-be existing) SPECIFICATIONS concerning array-evaluation in Excel (XLSX defining documents)? I found not to be. I though feel sure that the authors of OpenFormula did not fail to be compatible with Excel concerning the fundamentals of array-evaluation. Or - does somebody assume an accumulating function might be declared to return 1-1-array in the standard case? In this case subchapter 3.3, statement 2.2.1) of OpenFormula would apply. Also consider the analogy with SUM. To get finally convinced, try something like {=TEXTJOIN(", ";FALSE();A1;A1:B4;A7:B8)} (Appologies for my stubbornness. This touches a relevant question of priniples, imo.)
Can please someone with access to the relevant Excel version check the results instead we cluttering up this bug with assumptions whatsoever? Thank you.
(In reply to Eike Rathke from comment #13) AFAICS relevant Excel version is Excel365-2016. Useless versions are: Excel Online does support TEXTJOIN, but does not support arrays. Excel 2016 offline doesn't support TEXTJOIN (unless Microsoft puts it in a future update).
(In reply to Winfried Donkers from comment #14) >... > AFAICS relevant Excel version is Excel365-2016. > > Useless versions are: > Excel Online does support TEXTJOIN, but does not support arrays. > Excel 2016 offline doesn't support TEXTJOIN (unless Microsoft puts it in a > future update). Still insisting: If Excel (365-2016 or whatever version) is returning an array in the mentioned case this is surely a bug and will be fixed by them with one of the next updates.
(In reply to Wolfgang Jäger from comment #15) Comment #13 states the currently needed information. Until that information is present, no substantive statements with respect to the filed bug report are possible.
See this thread in OpenOffice forum and the links provided.
Hi Gerald, did you miss to paste the url?
(In reply to Dennis Roczek from comment #18) > Hi Gerald, > > did you miss to paste the url? Oops, sorry. here it is : https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=88407
Finally something that sheds light.. there you linked an .xlsx https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1307-1311.xlsx (why didn't you post that link here as well?) which when opened without recalculation on sheet '(1309an)' in cell D5 has a TEXTJOIN in array context that delivers result in one cell and when recalculated spreads over several rows. The Excel behaviour makes sense and we should do the same. Btw, your comment there > Developers persist in denying evidence. really pisses me off. No one denied evidence here, it's just that nobody came up with any evidence so far.
I'll take care of this.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f0fef88b747cc4f4de7a5ccb93da9c062545dcfb Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/36742 for 5-3 https://gerrit.libreoffice.org/36743 for 5-3-3 https://gerrit.libreoffice.org/36744 for 5-2 https://gerrit.libreoffice.org/36746 for 5-2-7
(In reply to Eike Rathke from comment #20) > Finally something that sheds light.. ... ??? > Btw, your comment there > > Developers persist in denying evidence. > really pisses me off. ... Would you mind to act your age? In fact the arguments an examples were rather clear for a long time now. Thanks for the fixing!
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1632b5cb4684834b1f4ce47ef47a827d2809ab61&h=libreoffice-5-2 Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT It will be available in 5.2.8. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-2-7": http://cgit.freedesktop.org/libreoffice/core/commit/?id=611e94e56159f2753f1753c3d78ba1b528196c18&h=libreoffice-5-2-7 Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT It will be available in 5.2.7. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9e76cc9da61dbe8ee5bbcac5c4378c5ba4fb4d21&h=libreoffice-5-3 Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT It will be available in 5.3.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c4ad6a24329446b374a7f01ab9a2214219d3590e&h=libreoffice-5-3-3 Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT It will be available in 5.3.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Commit Notification from comment #28) > Eike Rathke committed a patch related to this issue. > It has been pushed to "libreoffice-5-3-3": > > http://cgit.freedesktop.org/libreoffice/core/commit/ > ?id=c4ad6a24329446b374a7f01ab9a2214219d3590e&h=libreoffice-5-3-3 > > Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT > > It will be available in 5.3.3. > > The patch should be included in the daily builds available at > http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More > information about daily builds can be found at: > http://wiki.documentfoundation.org/Testing_Daily_Builds > > Affected users are encouraged to test the fix and report feedback. Tested with V5.3.3.1RC. Bugfix not working there. See new attachment. I reopened the bug therefore despite the fact that I did not test with any different vesrion.
Created attachment 133186 [details] Demo: Bug still living in V5.3.3.1RC
(In reply to Wolfgang Jäger from comment #30) > Created attachment 133186 [details] > Demo: Bug still living in V5.3.3.1RC Works forme with : Version: 5.2.7.2 Build ID: 2b7f1e640c46ceb28adf43ee075a6e8b8439ed10 Threads CPU : 2; Version de l'OS :Windows 6.0; UI Render : par défaut; Locale : fr-FR (fr_FR); Calc: single
Created attachment 133209 [details] File with TEXTJOIN entered as array formula, saved by Excel365 For comparison: I opened attachment 124781 [details] in Excel365-2016 and saved it as xlsx document. Note: both when saving the ods as xlsx and when saving the xlsx after a small change, I got a warning, which translates to something like "TEXTJOIN_CSE.xlsx contains calculation settings that are not supported in this file type. With some formulas different results may be returned after saving. Do you want to continue?" This warning does not seem to be related to TEXTJOIN, as the warning still appears after removing all TEXTJOIN formulas.
Works now for me, too with V5.3.3.2RC. Set to RESOLVED FUXED again.