Bug 148434 - LibreOffice Calc Formatting Option for Leading Zeros in Numbers Does Not Format Correctly
Summary: LibreOffice Calc Formatting Option for Leading Zeros in Numbers Does Not Form...
Status: CLOSED INVALID
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.4.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Number-Format
  Show dependency treegraph
 
Reported: 2022-04-07 01:50 UTC by Jerry Sussman
Modified: 2022-04-08 13:26 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Screenshot Help - LibreOffice Calc (248.98 KB, image/png)
2022-04-07 01:52 UTC, Jerry Sussman
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jerry Sussman 2022-04-07 01:50:10 UTC
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."
Comment 1 Jerry Sussman 2022-04-07 01:52:58 UTC
Created attachment 179365 [details]
Screenshot Help - LibreOffice Calc
Comment 2 m.a.riosv 2022-04-07 17:51:46 UTC
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.
Comment 3 Jerry Sussman 2022-04-07 21:21:36 UTC
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
Comment 4 Jerry Sussman 2022-04-07 21:22:04 UTC Comment hidden (obsolete)
Comment 5 QA Administrators 2022-04-08 04:21:43 UTC Comment hidden (obsolete)
Comment 6 Laurent BP 2022-04-08 06:49:15 UTC
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.
Comment 7 Eike Rathke 2022-04-08 13:26:12 UTC
(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."