Hex Display in Calc

English
, ,
#1

I’m trying to do some math in hex. I would like the user to be able to enter the data in hex, and have the data display in hex. But this doesn’t seem to be well supported in the number formatting.

The hex cells should append an indicator that the values are hex, such as prefix of “0x” or a suffix of “16” where the 16 is a subscript. The function dec2hex returns hex text, which seems to make the format not function properly. [00"16"] for example. I also tried ["0x"00] (without the square brackets of course). The result is just the hex digits such as 8F or 74.

The content of the cells are a hex value as text, or a formula such as
=DEC2HEX(255 - HEX2DEC(K3))

How can I add notation to these cells to indicate they are hex?

#2

Ctrl+M removes hard formatting, so text values such as Hex are left-aligned whereas numbers are right-aligned.
Ctrl+F8 highlights all values in blue and text in black font.

#4

I don’t follow your language. What is “hard formatting”? What are “values” as opposed to text. Do you mean numbers?

I don’t follow how this relates to my question. What am I missing?

#6

Hard formatting = Using manual formatting properties.
You can format a cell by a Style or by the manual formatting method: when you need click the bold, italic, color, etc… icons on the toolbar one-by-one.

#8

In this case the “values” means “numeric values”.
(The numeric value of a textual (string) data is 0 in the Calc application.)

#10

t87843.ods (23.4 KB)

Many users apply centered formatting which makes text visually indistinguishable from numbers as shown in columns G and H.
First column A has constant numbers in unformatted cells.
B has the corresponding hex values from Dec2Hex in unformatted cells. Spreadsheets can not calculate with hex numbers. Hex values are text. There are no more than 3 data types number, text and error values returned from formulas. Dates are day numbers, times are fractions of days, currencies are just plain decimals, TRUE equals 1, FALSE equals 0.

The text in column C is formatted with a user-defined number format as suggested by @erAck. This is one of the rare cases where a number format makes sense with text values. It makes the hex strings distinguishable by an additional “0x” prefix, however you should be aware that the values in columns B and C are identical although the are displayed differently. See test in column E. Number formats change only the appearance of a cell value.
The in column D is a concatenation of the string “0x” with the result of Dec2Hex. This actual text is identical to the displayed text. Column D is not identical to the equally looking text in column C. See test in column F.
Unformatted cells show numbers right-aligned and text right-aligned which makes them visually distinguishable.

Every Calc user should know about menu:View>“Highlight Values” [Ctrl+F8] which displays numbers in blue, text in black and formula results in green font.

P.S. I would prefer the concatenated text “0x42” over the formatted text “42” displayed as “0x42” because it is very unusual and irritating when a text value lies about itself.

#3

There is no hex display number format. But you can use ="0x"&DEC2HEX(...)
Or for numbers not supported by DEC2HEX or HEX2DEC the DECIMAL(…;16) and BASE(…;16) functions to convert between.

#5

I’m very much aware the formatting of hex numbers is not provided in Libreoffice Calc. That’s why I’m jumping through the hoops of using DEC2HEX, etc.

When printing a number using a custom format, “0x” can be prepended. Is that not possible with text?

#7

The hexadecimal numbers are handled as Strings in the Calc application (but not as numeric values). (At least the result of the DEC2HEX() function)
You can append the “0x” String to the result of the function HEX2DEC() - as erAck shows you above.

#9

Format code: "0x"@

If that’s not what you want then I don’t understand your question.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.