https://bugs.documentfoundation.org/show_bug.cgi?id=173668
--- Comment #16 from ady <[email protected]> --- (In reply to Regina Henschel from comment #15) > (In reply to ady from comment #12) > > > For Strict (i.e. second argument equivalent to "1"), ARRAYTOTEXT() should > > use the settings in menu Tools > Options > Calc > Formula > Separators ... > > That generates the problem, that the resulted text is not compatible with > Excel. That means, that a function that has the result of ARRAYTOTEXT() as > input, will give different results than in Excel. > > I personally like the flexibility of LibreOffice. I use the same delimiters > as specified for the ODF file format, which is ; between columns and a | > between rows. However, we need to find a way be compatible with Excel. For the function(s) in question, there is no way to be absolutely compatible with Excel in every sense in every single case. Let me explain. The main goal of the Strict format in ARRAYTOTEXT() is to obtain a text that could be potentially used "as-is" in the formula bar input window (adding/starting with an equal symbol). If Calc wants to keep this goal, then the function has to return a text that complies with Calc settings. A spreadsheet in ods file format being created and used only in Calc, would need to respect the aforementioned settings in order to keep that goal. Then we might have a spreadsheet in _xlsx_ file format being created and used only in Calc. Even if a particular file were to be used only in Calc, this case would need to consider the possible case in which an xlsx file is opened by Excel. And finally we have the possibility that Calc would need to open an xlsx file that was created by Excel. The last 2 cases involve exporting and importing to/from xlsx. But that is a follow-up issue. The initial case should be that Calc, with an ods file, would need to respect its own settings. Then the export and import processes would need some parsing from/to Calc settings, so an xlsx file could be potentially opened and used by both, Excel and Calc. So, there are multiple steps (or a chain of reports, if you want) in order to correct the current behavior. If we keep the current situation, then a file in Calc would work correctly only by chance, when the delimiters' settings would happen to coincide with those being used by Excel's ARRAYTOTEXT() (in that file?). -- You are receiving this mail because: You are the assignee for the bug.
