It is impossible to enter for example "'1" as cell content via keyboard. Steps to reproduce: - Entering "''1" leads to "''1" (Note: Do not enter the double quotation marks!" Expected result: Entering "''1" should lead to a cell content of "'1". If a digit as one or more leading single quotation marks, the first should single quotation mark should always be truncated. This is how Excel and Only Office behave.
*** Bug 149664 has been marked as a duplicate of this bug. ***
I think that a double apostrophe (U+27 U+27) at the beginning of a cell says that all cell content is text, and all cell content must be showed. For me this is not a bug. By the way, you can type: - "‘" (left single quotation mark, the same that U+2018) or "’" (right single quotation mark, the same that U+2019) - "1" and it would work fine. If AutoCorrect option "[x] Replace" for Single Quotes is checked, you can type: - "'" apostrophe (or, maybe, a space or whatever character except "=", "+", and "-") - "'" apostrophe (it is not the same than a single quotation mark, but in this position will be converted to a right single quotation mark)¹ - "1" Press: - F2 key (to enter edit mode) - Home key - Del key (to delete the space) - Enter ¹ A left single quotation mark if the first typed a space.
Or you can Format Cells - Number - Category - Text and '1 will work fine.
This is a bug. At least it incompatible with quasi standard for spreadsheet application. - Please Open the attached spreadsheet created by Only Office (same with Excel) - (Select cell A1 if not already selected) Note: Cell A1 has been entered "''1" by Only Office (same with Excel). LibreOffice displays correctly "'1" after opening it. - Edit the cell by adding on more "1" digit to the end. Result: Calc removes the "'" by adding the additional digit. As I already wrote above, cell A1 has been created by entering "''1" into the cell (same with Excel). But LibreOffice display "'1" as A1 string in the big edit field on top. Only Office correctly displays "''1" in the big edit field but"'1" in the cell. So LibreOffice breaks interoperability here.
It makes absolutely no sense, that the first "'" is always suppressed for every string but not if the second char is a "'" as well.
Created attachment 180910 [details] LibreOffice vs OnlyOffice (same es Excel).png
Created attachment 180911 [details] Testkit produced by OnlyOffice (same with Excel)
I would support behavior change, when any data entered in number-formatted cell, when started with an apostrophe, would consider that starting apostrophe as "the following is a text" indicator, irrespective of the "can the following be converted to number or not".
Code pointer: ScColumn::ParseString [1]. The current code does lots of stuff after seeing the leading '. However, the opposite case must be considered as well, mentioned in comment 4: when entering edit mode for a text that starts with an apostrophe, we need to prepend another apostrophe, as we do for "number-as-a-text" case. This is handled in ScTabViewShell::UpdateInputHandler [2] and lcl_GetInputString [3]. [1] https://opengrok.libreoffice.org/xref/core/sc/source/core/data/column3.cxx?r=20b8c7f3#2076 [2] https://opengrok.libreoffice.org/xref/core/sc/source/ui/view/tabvwsha.cxx?r=20b8c7f3#666 [3] https://opengrok.libreoffice.org/xref/core/sc/source/ui/unoobj/cellsuno.cxx?r=20b8c7f3#1361
Fun thing is, that FormulaLocal property and getFormula method already return doubled apostrophe (although incorrectly): ThisComponent.Sheets(0).getCellByPosition(0,0).formulaLocal = "'abc" msgbox ThisComponent.Sheets(0).getCellByPosition(0,0).formulaLocal This code will output ''abc, while the cell and input line will only show one apostrophe (as in the string set in the first line).
@Mike: Thank you very much for looking into the code. Are you able to write a patch?
(In reply to OfficeUser from comment #11) No, unfortunately I don't have spare cycles. That's why I added the code pointers, so *iif* Eike confirms this is a valid idea, anyone interested could try to resolve this easyhack.
Yes, an input of ''1 in a not as Text formatted cell should produce '1 that when edited is presented as ''1 again if the cell is not formatted as Text. I wouldn't change the behaviour though that an input of 'text is literally taken as text input without stripping the ' apostrophe. It works like this for decades and people are used to. Accordingly, ''text is literal, not replacing the double '' with a single '. There's no compelling reason to change that. Of course ThisComponent.Sheets(0).getCellByPosition(0,0).formulaLocal in the example giving ''abc is wrong in that context.
(In reply to Eike Rathke from comment #13) > I wouldn't change the behaviour though that an input of > 'text > is literally taken as text input without stripping the ' apostrophe. It > works like this for decades and people are used to. Accordingly, > ''text > is literal, not replacing the double '' with a single '. > There's no compelling reason to change that. I believe there is. You put the load to guess how the program would interpret a text on the user, with all the complex and not obvious rules behind Calc's decisions when parsing an input. A user entering a column of identifiers would meet one behavior entering '123e4, and a different behavior entering' 123i4. One would have to remember that TRUE (or a localized analog) needs ', but most other words don't. IMO making it really consistent and not depend on parsing result would be really beneficial. Thanks fir taking it!
I'll fix the bug first, we could still decide on a new behaviour then. Does Excel *always* strip the first apostrophe for content and prepends one when editing? (unless the cell is Text formatted I'd presume).
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/484448bc04edfbe22db784d2c68a679a3f98fbb9 Resolves: tdf#149665 Strip first ' also for multiple '' if following is numeric It will be available in 7.5.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 "libreoffice-7-4": https://git.libreoffice.org/core/commit/ff1c2cd5ea8049fe0dc4283a8990c67eb19b8219 Resolves: tdf#149665 Strip first ' also for multiple '' if following is numeric It will be available in 7.4.0.0.beta2. 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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5d875e4fbb490c2cd3bd75fa88a865333932dd97 tdf#149665: sc_ucalc: Add unittest It will be available in 7.5.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.
(In reply to Eike Rathke from comment #15) > I'll fix the bug first, we could still decide on a new behaviour then. Definitely! Thanks for fixing this. We may have a separate issue for the rest of discussion if you prefer. > Does Excel *always* strip the first apostrophe for content and prepends one > when editing? (unless the cell is Text formatted I'd presume). I can only test using Excel 2016. And I used two columns: A (default-formatted = "General" format), and B (pre-formatted as text). I put the same data into both columns (the double quotes below just mark the entered strings, I didn't put the double quotes themselves). A1 and B1: "'1" A2 and B2: "'true" A3 and B3: "1" A4 and B4: "true" A5 and B5: "'=1" A6 and B6: "=1" The result is: A1 and B1 look identical: "1" (and apostrophe appears in the formula bar) A2 and B2 look identical: "true" (and apostrophe appears in the formula bar) A3 has "1" right-aligned, and B3 left-aligned (and no apostrophe) A4 has a centered "TRUE", B4 has left-alighend "true" (no apostrophes) A5 and B5 look identical: "=1" (and apostrophe appears in the formula bar) A6 has a right-aligned "1", B6 has left-aligned "=1" (no apostrophes) So Excel seems to treat leading apostrophe *identically* everywhere, regardless of the cell's original format (even textual), and strips it from the content; it also has a special format flag (xf's quotePrefix), that tells if Excel should show the apostrophe with the cell data, allowing to show the apostrophe when the user entered that - even for cases where it's not required technically. I suppose that that is a bit too extreme. I would suggest to ignore (at least for now) the Excel's treatment of apostrophes in text-formatted cells, and only think about unification of apostrophe treatment in number-formatted cells. Which, I still believe, would benefit from *always* stripping one leading apostrophe from the rest that would be unconditionally be considered textual data. Also I suggest to ignore that quotePrefix thing. The "show apostrophe where it's needed for disambiguation" looks good enough to me.
Hi Eike, Hi Mike, thanks for for supporting this issue! > A4 has a centered "TRUE" I cannot reproduce this one neither with Excel, OnlyOffice nor Gnumeric. All show a left aligned "true" for both cases. @Mike: Can you double-check this with Excel please? In addition I checked OnlyOffice and Gnumeric for Mike's scenario. Result: - Both OnlyOffice and Gnumeric show the same cell-content results described by Mike for Excel. (Only exception for both is the centered "true" that I even cannot reproduce with Excel.) - OnlyOffice shows exact the same content for the formular bar as described by Mike for Excel. - Gnumeric shows the same content for the formular bar as described by Mike for Excel with with one EXCEPTION: B2 shows NO apostrophe in the formula bar. I will attache a screenshot.
Created attachment 181137 [details] onlyoffice_gnumeric_comparison.png
(In reply to OfficeUser from comment #20) > > A4 has a centered "TRUE" > > I cannot reproduce this one neither with Excel, OnlyOffice nor Gnumeric. > All show a left aligned "true" for both cases. You are probably not using an English locale. I assume your locale is German de-DE so you'd have to enter WAHR instead of TRUE. > - Gnumeric shows the same content for the formular bar as described by Mike > for Excel with with one EXCEPTION: B2 shows NO apostrophe in the formula bar. Same reason. Try with WAHR.
Hello Heike, Thank you very much for the hint. I have repeated the test, taking the language settings into account. This time I also used Planmaker as a further comparison software. I have made the results available in a screenshot. The summary: - Excel, OnlyOffice and Planmaker behave exactly identically for Mike's test scenario. - Gnumeric also has the same results as the above applications for the cell content. In some cases, an apostrophe entered in the edit field is missing. I would call this a bug in Gnumeric. - LibreOffice has some (more than Gnumeric) deviations from the quasi-standard (defined by Excel, OnlyOffice and Planmaker).
Created attachment 181165 [details] Spreadsheet comparison
(In reply to Commit Notification from comment #17) > Eike Rathke committed a patch related to this issue. > It has been pushed to "libreoffice-7-4": > > https://git.libreoffice.org/core/commit/ > ff1c2cd5ea8049fe0dc4283a8990c67eb19b8219 > > Resolves: tdf#149665 Strip first ' also for multiple '' if following is > numeric > > It will be available in 7.4.0.0.beta2. > > 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. @Heike: I generated an appimage from Version: 7.5.0.0.alpha0+ / LibreOffice Community Build ID: 1f201d76d6e2fcc9d8af6504c38bd98c46e0798e CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: de-DE (de_DE.UTF-8); UI: en-US Calc: threaded I think this is such a daily build you mention. Isn't it? For me it looks like your patch is NOT integrated there.
Sorry, I mean Eike. Btw... the bugtracker should feature an edit function.
(In reply to OfficeUser from comment #25) > I think this is such a daily build you mention. Isn't it? Looks like. > For me it looks > like your patch is NOT integrated there. What do you deduce that from?
Hi Eike, >> What do you deduce that from? I interpreted your statement below to mean that at least the initial reported bug should be fixed in your patch. In my test build the problem (cannot enter a single apostrophe) still exists. Now, when I check again, I notice a change in the test build: The two apostrophes now consist of different characters; take a closer look. I will attach a screenshot. >> I'll fix the bug first, we could still decide on a new behaviour then. Now, however, Mike's test scenario has revealed even more related deviations from common practice in the world of spreadsheet programs. (See orange highlighted boxes in the "Spreadsheet comparison" attachment). I am of the opinion that we should follow these practices with LibreOffice as well, since they are widely used and also seem more intuitive or reasonable than the current as-is state with LibreOffice Calc. A patch that fixes all these things (see orange cells) would be desirable in my opinion. Do you guys (Eike, Mike) share this opinion? For example, a right instead of center aligned TRUE/WAHR makes no sense because it is not a number.
Created attachment 181192 [details] different apostrophe chars caused by patch?
No, you are hit by AutoCorrection's single quotes replacement. Disable under Tools -> AutoCorrect Options..., tab Localized Options, Single Quotes.
(In reply to OfficeUser from comment #28) > For example, a right instead of center aligned TRUE/WAHR makes no sense > because it is not a number. It is a number, just formatted. 0 is displayed as FALSE, anything else as TRUE. Excel has a distinct boolean type and treats that differently.
Hello Eike, thank you very much for your feedback and the explanations. However, I was already aware of how TRUE and FALSE are handled in LibreOffice. Well, I think it is not crucial here that there are values behind the words TRUE and FALSE. It has become established practice that *NUMERIC VALUES* in tables are right-justified. The only reason for this is that the number of decimal places becomes clear to the viewer more quickly. But now it is a question of how the *WORDS* TRUE or FALSE should be displayed. And for this I think centered is an absolutely reasonable default, which we should follow with LibreOffice. Regards Norbert
Whatever, it's not part of this bug.
@Eike: OK, request for centered TRUE/FALSE is https://bugs.documentfoundation.org/show_bug.cgi?id=149941 I could NOT put you on CC of this bug unfortunately. There is always an error message.
(In reply to Eike Rathke from comment #30) > No, you are hit by AutoCorrection's single quotes replacement. Disable under > Tools -> AutoCorrect Options..., tab Localized Options, Single Quotes. Thanks for the hint Eike. I have disabled it for my dev build appimage. Do you have any idea, why I did not need to disable it form my "normally installed release build"? Test result: - You patch WORKS for numbers, for example "''1234" - Your patch does NOT work for non number strings, for example "''car"
(In reply to OfficeUser from comment #35) > - Your patch does NOT work for non number strings, for example "''car" It does not claim it would.
(In reply to Eike Rathke from comment #36) > (In reply to OfficeUser from comment #35) > > - Your patch does NOT work for non number strings, for example "''car" > It does not claim it would. Hi Eike, thanks so far for your initial patch. I just have reported what I found out. Would you support a patch that fully aligns apostrophe behavior with other popular spreadsheet programs? If so, are you ready to revise your patch again?
I'm not convinced of changing the behaviour for cells already formatted as Text. People are used to *any* input entered ending up literally, including any number of leading apostrophes. If we suddenly started to strip one it would be broken. I do see however that for cells not formatted as Text a uniform handling would be good.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/939724fe6abd16015dbef6c476f7f57a2dc466d8 Related: tdf#149665 Unify input of a leading ' apostrophe in non-Text cell It will be available in 7.5.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.
Hello Eike, first of all many thanks for your patch. Unfortunately, however, it did not bring us to our goal. The current situation is that the result with two single quotes is: ''3 See attachment "lo_issue_149665.png". Please also note that both single quotes are different characters. I see this on both Linux and Windows with default settings! Version: 7.4.1.2 / LibreOffice Community Build ID: 40(Build:2) CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: de-DE (de_DE.UTF-8); UI: de-DE Ubuntu package version: 1:7.4.1~rc2-0ubuntu0.18.04.1~lo3 Calc: threaded
Created attachment 182679 [details] lo_issue_149665.png
(In reply to OfficeUser from comment #40) > Version: 7.4.1.2 / LibreOffice Community (In reply to Commit Notification from comment #39) > It will be available in 7.5.0. > The patch should be included in the daily builds available at > https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. Please check the daily builds of current development branch.
Eike's patch is already included 7.4.1.2. I already have checked post-patch daily builds some time ago with the same negative result. This Regression has been introduced with that patch. I am rather sure. Previous versions did not display different characters.
(In reply to OfficeUser from comment #43) > Eike's patch is already included 7.4.1.2. It is not. Only the one from comment 17 is. You were just repeating comment 29 and comment 30.
Interestingly, OOo 3.3 would already strip that first apostrophe. Some kind of regression? Would have happened before 6.0.7.3. In any case, verified new behaviour in text and non-text cells: Version: 7.5.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: 6d6a2343b1d45695f3ea02818d317a022a7b259f CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Thanks Eike!
*** Bug 142050 has been marked as a duplicate of this bug. ***