Description: When I format a spreadsheet cell to set the number of leading zeros for a number (N), the resultant format is not the number of leading zeros in the number N. Rather, the format is the number of digits to the left of the decimal at which a leading zero, if any, will appear. E.g., if I format the number of leading zeros at 4, the number 01234 will be formatted as 1234. However, if I format the number of leading zeros at 5, the number 01234will be formatted to appear correctly as 01234. Global formatting of the cells of a spreadsheet containing numbers with different numbers of digits to the left of a decimal point that are preceded by one or more leading zeros (e.g., 01, 001, 0001, 00001, etc.) now is practically impossible. To be accurate, the total number of digits to the left of a decimal point within each cell now would need to be counted, and--unless all cells contained numbers with both the same number of digits and the same number of leading zeros--to be separately formatted to make sure that the correct number of leading zeros are selected. Making matters worse, even if one painstakingly counts the numbers of digits to the left of the decimal point and the number of leading zeros for each number, and formats each cell accordingly, the efforts may be for naught if another LibreOffice Calc formatting setting later is applied. Thus, e.g., in the case of "Autoformatting" an otherwise properly formatted spreadsheet to enhance visual appeal (e.g., different color backgrounds for every other row and column) the number of leading zeros for every cell will be modified in accord with the Autoformatting rather with the cell formatting set by the user. Steps to Reproduce: 1.Open LibreOffice Calc; 2.Open Spreadsheet; 3.Enter number with one or more leading zeros in one or more cells; 4.Select one or more cell containing number with one or more leading zeros; 5.Select Format; 6.Select Numbers; 7.Select Options; and 8.Select Leading Zeros. 9.Enter number of Leading zeros. Actual Results: Cells containing one or more numbers with leading zeros were not formatted to display the number of leading zeros selected. Instead, the cells were formatted to display the number of digits to the left of the decimal point at which a leading zero, if any, would appear. Expected Results: The number of leading zeros selected (LibreOffice Calc>Spreadsheet>Format>Cells>Numbers>Options>Leading zeros) is the number of leading zeros regardless of the number of digits to the left of the decimal point that the number has. Reproducible: Always User Profile Reset: Yes Additional Info: I have been using and enjoying LibreOffice (and its Sun Office progenitor) for decades, in various Linux distributions. Insofar as I am aware, the "leading zero" feature does not work as it did in previous versions of LibreOffice Calc. Last week, I switched to the MX-21 XFCE Wildflower distro from Linux Mint (Mate). I discovered this "bug" through happenstance while proofing a spreadsheet containing numerous account data. See attached for "Help - About LibreOffice."
Created attachment 179365 [details] Screenshot Help - LibreOffice Calc
Please attach a sample file. There are no numbers with leading zeroes not coming from the format. If you see that, then maybe they are numbers in a text format cell, or numbers with a leading single quote like '0123 treated as text not numbers.
Greetings, and thank you for the prompt reply. Perhaps we are having a communication issue? I did not say nor mean to suggest that the Format feature did not produce "Leading zeros." Rather, what I said and meant to suggest that the feature did not produce the number of "Leading zeros" selected, but instead produced leading zeros only if the number of leading zeros selected was greater than the number of digits to the left of the decimal point in the number within the cell being formatted. Previously, if I selected "Number of Leading Zeros" as 1, 2, 3, etc., "Leading Zeros" (1, 2, 3, etc.) would be placed to the leftmost position of the last significant digit to the left of the decimal point regardless of the number of digits in the number within the cell being formatted. Thus, if the number of leading zeros selected was "2," 2 zeros would precede each number, whether the number was "01," 02," "001," "1," "2," "10," "1000," or whatever. The number 01 would appear as 001, the number 10 would appear as 0010, the number 0001 would appear as 001, the number 000000007 would appear as 007. Now, if the number of "Leading zeros" selected is 1, 2, 3, etc., one or more leading zeros (1, 2, 3, etc..) will only appear if the number of digits to the left of the decimal point in the number in the cell to be formatted is less than the number of leading zeros selected. Here are literal examples just taken in the case of the formatting of numbers to contain "2 Leading zeros." Example A: 1 will appear as 01 Example B: 10 will appear as 10 Example C: 100 will appear as 100 Example D: 1000 will appear as 1000 Example D: 01 will appear as 1 Example E: 001 will appear as 1 Example F: 0001 will appear as 1. How in the world is this not a bug? Respectfully, I prefer not to attach another spreadsheet. Moreover, in the absence of my ability to delete the attachment to my initial report, for the reasons that follow, I respectfully request that you do so. The software used to take the screenshot of the LibreOffice Help-About (XFCE-4 Screenshooter) itself contains a bug: I clearly and unequivocally took a screenshot only of the portion of my display that contained the "LibreOffice Help-About" with its opaque background. I attached same with the expectation that the image taken and saved was the image that I took. Instead, The MX-21 XFCE-4 Screenshooter "X-rayed" the LibreOffice Help-About opaque background and captured a portion of the open spreadsheet that I then was working on. Though I would have preferred that no spreadsheet information had been captured, I am grateful that no account numbers or passwords were uploaded. Still, I do not think that the attachment should be visible to all as it contains information that was not in the screenshot as taken. Thanks again...Jerry Sussman
[Automated Action] NeedInfo-To-Unconfirmed
Hi Jerry, Could you please attach a sample file? Leading zeros work as I expect on my configuration. Your procedure is not clear how you apply "leading zeros" format. With a sample file it could be clearer.
(In reply to Jerry Sussman from comment #3) > Previously, if I selected "Number of Leading Zeros" as 1, 2, 3, etc., > "Leading Zeros" (1, 2, 3, etc.) would be placed to the leftmost position of > the last significant digit to the left of the decimal point regardless of > the number of digits in the number within the cell being formatted. No, that never was the case. > Thus, > if the number of leading zeros selected was "2," 2 zeros would precede each > number, whether the number was "01," 02," "001," "1," "2," "10," "1000," or > whatever. The number 01 would appear as 001, the number 10 would appear as > 0010, the number 0001 would appear as 001, the number 000000007 would appear > as 007. It didn't. This is a misunderstanding of how number formatting works. A format of 00 (two "leading" zeros) tells to fill the display of numbers with 0 to the left for *up to* 2 digits. Hence a one digit number 0 to 9 displays as 00 to 09 and any number with two or more digits gets *no* 0 prefix. The 00 are *placeholders*. See also https://help.libreoffice.org/7.3/en-GB/text/shared/01/05020301.html?DbPAR=SHARED#bm_id3153514 Placeholders Explanation, 0 (Zero): "Displays extra zeros if the number has less places than zeros in the format."
Michael Meeks committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/50a08d1eb47f8faeb511cec43d1f8ba12b8f27f7 tdf#148434 - avoid strange OS/X deadlock around AnyInput. It will be available in 7.6.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.