Menu item sequence >Tools>Options>LibreOffice Calc>Formula> leads to a dialogue window containing a section "Separators". There are the three mentioned input fields. They don't offer a restricted selection, but allow for direct input. Some characters are not accepted, among them letters and the ordinary arithmentic operators, but many are accepted even if obviousliy not usable for the purpose. Among the improper characters are all decimal digits ":", "~", "!" range operators "&" the concatenation operator "=" a comparison operator "%" an arithmetic suffix operator "#","$", "?" otherwise improper and even "{", "}". The only characters I can find for a positive list are ";" and (hesitating) "|", and -if you think it's unavoidable- the damned comma which surely was at the beginning of the mess. Suggested solutions: 1) Simple: The fields accept only ";", "|", and "," shown in a label. 2) Comfortable: The selection is made by enabling the respective radio button from a group. In any case the second and the third choice must be different (as already implemented).
I missed to mention a suggestion: The defaults should be the characters used for the respective purpose in the persistent representation. These are ";" between parameters (labelled "Function") ";" between elements in a row (Labelled "Array column") "|" between the rows ("Array row").
Agree, but I find personally more understandable visually: "|" between elements in a row (Labelled "Array column") ";" between the rows ("Array row")
(In reply to m.a.riosv from comment #2) > Agree, but I find personally more understandable visually: > "|" between elements in a row (Labelled "Array column") > ";" between the rows ("Array row") Well, it may be seen differently. I personally would see it the other way. BUT: I simply suggested to do did it as ODF "Recalculated Formula" defines the separators for the persistent (file) representation in "5.13 Inline Arrays".
That once was the case, but users complained that they can't enter formulas like they were used to from Excel, so separator settings were introduced. Which does make sense.
(In reply to Eike Rathke from comment #4) > That once was the case, but users complained that they can't enter formulas > like they were used to from Excel, so separator settings were introduced. > Which does make sense. I simply want to make clear again that the bug report was concerned about IMPROPER (not working, not acceptable) settings. The line of discussion what probably was better in old times ran in parallel. Just testing with V 7-0.3.1RC I got again that a setting like 'Function : 1 'Array Column' : ? 'Array rRow': } (Curly bracket) was accepted and saved despite the fact that these settings not can be expected to allow functional and/or reasonable usage. (A "working" example with the kind of setting is shown 'local' as "=TEXT(11"0")" e.g. It returned 1.)
A very restricted set is necessary for the function parameter separator that must not equal any other operator (or (one letter) function name). As expressions are not evaluated in inline arrays the array separators are less restricted, e.g. ':' or other otherwise operators *are* possible, '{' and '}' or digits of course not. Maybe disallow '%' to prevent confusion.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/bb54d6d8241a06a6772052b77b67d6a4f686426c Resolves: tdf#127013 differentiate non-/array separators and restrict It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://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 "master": https://git.libreoffice.org/core/commit/381bc9d9acd461415cf81eef71ec3dd935a07b62 Related: tdf#127013 Check loaded separators for validity It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/117075 for 7-1
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-1": https://git.libreoffice.org/core/commit/304d64cc27a729646522c7e16ad3d1d0aa2b1a12 Resolves: tdf#127013 differentiate non-/array separators and restrict It will be available in 7.1.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.