Bug 45952 - Format Cells: Binary and Hex with digit grouping
Summary: Format Cells: Binary and Hex with digit grouping
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.5.0 release
Hardware: All All
: low enhancement
Assignee: Not Assigned
Keywords: needsDevEval
Depends on:
Blocks: Number-Format Cell-Format-Dialog
  Show dependency treegraph
Reported: 2012-02-12 05:04 UTC by Valerio Messina
Modified: 2020-04-27 10:43 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Basic macro which has needed functionality (20.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-12-13 12:59 UTC, sasha.libreoffice

Note You need to log in before you can comment on or make changes to this bug.
Description Valerio Messina 2012-02-12 05:04:55 UTC
Menu Format, Cells, tab Numbers, Category list: Binary and Hex

Binary should postpend a small 'b', hex should prepend a '0x'.

Both should have "digit grouping" that is similar to decimal thousand
separator, but happen every 4 digit.
Comment 1 Joel Madero 2012-12-11 19:13:21 UTC

Marking as:
New (confirmed)
Enhancement (leave it as is)
Priority (low, won't be useful for most users but for a limited group will be useful)

Whiteboard status: ProposedEasyHack

Thanks for the suggestion and for helping us make LibO a better office suite for everyone
Comment 2 Valerio Messina 2012-12-12 17:10:01 UTC
for "Version:" field, we should set 3.6 or next?
Comment 3 Joel Madero 2012-12-12 17:12:35 UTC
Version is always the version that the bug was originally found in OR the version that the enhancement was first thought of. If this was 3.5 go ahead and leave it. There is always confusion about what version means but it does mean the oldest, not the newest or latest, version, where the request was made against
Comment 4 Valerio Messina 2012-12-12 17:23:31 UTC
anyway thank you for considering this enhancement useful for electronics
Comment 5 sasha.libreoffice 2012-12-13 12:59:30 UTC
Created attachment 71444 [details]
Basic macro which has needed functionality

I have written formula on Basic Macro. It produces text that looks like correct hex and bin numbers. But because of it is text, no further calculations with such numbers are possible. Intended only for final output.
Only 31 bit numbers allowed. Alas. No 64 bit.
Comment 6 Lionel Elie Mamane 2013-05-31 05:15:04 UTC
(In reply to comment #0)
> Menu Format, Cells, tab Numbers, Category list: Binary and Hex
> Binary should postpend a small 'b', hex should prepend a '0x'.

IMHO the "binary" and "hexadecimal" format characters should not prepend anything by themselves; any constant string can be added in the format as such.

E.g., if we make the "binary digit" format character "b" and the "hex digit" format character "x", then the effect you want to achieve can be had by:


But if one wants Ruby-compatible formatting, one can use:


And if one wants "like Fortran", one can use:


Like ADA? Easy peasy:


etc, etc.

If we implement that, we could as well add octal (base 8) to the mix.

Actually, we could take inspiration from Ada and just allow an arbitrary base in number formats...

> Both should have "digit grouping" that is similar to decimal thousand
> separator, but happen every 4 digit.

Hmm... That's more difficult to achieve with my "arbitrary base" idea, but maybe this should also be arbitrarily configurable? <shrug>
Comment 7 Valerio Messina 2013-05-31 10:47:33 UTC
a general base solution is best but maybe hard, while hex and binary are very useful in engineering and not too hard to implement, many could settle for those
Comment 8 Eike Rathke 2013-06-28 15:04:36 UTC
Keep in mind that if this was implemented we'd also need to define how to store it in ODF. Does Excel have such thing in its number formats?
Comment 9 Joel Madero 2013-06-28 16:00:11 UTC
Indeed - we may need to close this and recommend Valerio get in touch with the ODF committee to see if they want to implement his.

Eike - agree with this? Really we can't do anything until ODF supports it.
Comment 10 Joel Madero 2014-02-27 22:55:11 UTC
In order to limit the confusion between ProposedEasyHack and EasyHack and to make queries much easier we are changing ProposedEasyHack to NeedsDevEval.

Thank you and apologies for the noise
Comment 11 Robinson Tryon (qubit) 2015-12-13 11:20:47 UTC Comment hidden (obsolete)
Comment 12 Shriramana Sharma 2019-08-24 06:38:37 UTC
Hello to all developers. This is really a useful needed addition to be very useful to people in electronics-related disciplines. If it needs to be handled at ODF level then please take it to that level on behalf of us users.

The currently available DEC2BIN, DEC2HEX aren't *that* useful for this requirement because you can't do arithmetic operations on those, even simple addition/subtraction, or fill series operations by dragging bottom right handle of selection etc.

To be able to do such addition etc you have to have a separate row/column/table of the decimal numbers with the operations and a parallel row/column/table running the DEC2x functions to get the desired display. This is inelegant as it causes cluttering of the data and makes things more difficult overall.

If Excel doesn't have this yet then ODF/LibO can be the first on scene!

BTW I also agree that LibO should not by itself prepend any 0x or 0b etc and if the user wants, they can add it as mentioned in comment #6.

I hope this gets added soon!