Bug 108338 - Numeric formatting using the _ does not work
Summary: Numeric formatting using the _ does not work
Status: RESOLVED DUPLICATE of bug 152724
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Number-Format
  Show dependency treegraph
Reported: 2017-06-05 00:09 UTC by Paul Goyette
Modified: 2023-02-27 08:30 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Paul Goyette 2017-06-05 00:09:46 UTC
In the "other" spreadsheet program, I would be able to apply a format of '#,##0_._0_0' to request that space for a decimal point and two fractional digits be reserved, but not printed; with horizontal-alignment of "right" the above format would align correctly with numbers formatted at '#,##0.00'.

However, with Calc, the _ formatting directive doesn't seem to work. It allows me to create a user-defined format, but when the document is saved and then reloaded, all formats with the _x type of directive are replaced with modified formats that have trailing quoted spaces instead of the _x directive(s). And the number of spaces does not seem to be correct for ensuring vertical alignment.

Steps to Reproduce:
1.Format a number using the _0 (or any other _x)
2.Save document, exit program
3.Restart program, open document, observe formatting changes

Actual Results:  
format string for affected numeric fields is changed to include "   " (number of spaces varies).

Expected Results:
Specified formatting (with the _ directive) should be retained.

Alternatively, if the conversion to "  " formatting is absolutely essential, at least the amount of space being inserted should correspond to the intended behavior (ie, proper alignment).

Reproducible: Always

User Profile Reset: No

Additional Info:
According to Mike Kaganski, he has reproduced this on and requested that I file this bug report.

User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0
Comment 1 Mike Kaganski 2017-06-05 17:19:55 UTC
Reproducible with Version: (x64)
Build ID: 3d9a8b4b4e538a85e0782bd6c2d430bafe583448
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Layout Engine: new; 
Locale: ru-RU (ru_RU); Calc: group

According to https://help.libreoffice.org/Common/Number_Format_Codes#Spaces , this must work.

Actually after save-and-reload, a "      " with twice as much spaces is created in format code instead of _._0_0 part (as if separate space for both "_" and following character).
Comment 2 QA Administrators 2018-10-09 02:52:23 UTC Comment hidden (obsolete)
Comment 3 Paul Goyette 2018-10-09 03:04:45 UTC
This bug is still present as of version (built from sources on NetBSD 8.99.22 host, x86_64 platform).

A format of ###_._0_0 gets converted to ###"      " by a save/open sequence.
Comment 4 QA Administrators 2019-10-10 02:40:21 UTC Comment hidden (obsolete)
Comment 5 Paul Goyette 2019-10-10 14:49:54 UTC
This bug is still present in:

   Build ID: 30(Build:2)
   CPU threads: 16; OS: unknown; UI render: default; 
   Locale: en-US (C); UI-Language: en-US
   Calc: threaded

This build was compiled from sources using NetBSD pkgsrc on an amd64 host running NetBSD 8.99.51.
Comment 6 Eike Rathke 2019-10-16 14:52:15 UTC
The problem here is that an underscore plus character leads to a *visual* replacement with an approximated width of that character, i.e. for '_.' one space is used and for '_0' two spaces are used. The ODF file format has no notion of this visual relation and no <number:visually-blank> element or such. When storing the definition of the number format the number formatter is asked for the representation, which for the '_._0_0' sequence results in 5 blanks. The #,###_._0_0 format is stored as

    <number:number-style style:name="N121">
      <number:number number:decimal-places="0" loext:min-decimal-places="0" number:min-integer-digits="0" number:grouping="true"/>
      <number:text>     </number:text>

with 5 literal spaces in the trailing <number:text> element. Technically when displaying a number formatted such the result is the same as with _._0_0
Comment 7 Laurent Balland 2023-02-27 08:30:16 UTC

*** This bug has been marked as a duplicate of bug 152724 ***