Description: I have the formula '=TEXT(DEC2HEX(<number>);"#")'. For numbers "1" to "479" the result is "1"-"1DF". For 480 it is "1", 481 - "10", 482 - "100", and so on. Starting with 490, "1EA" is correct again. The next strange range is from 736 to 745 ("2" - "2,000,000,000"). Try numbers between 11776 and 11929: you will find following groups of 10 wrong formatting and 6 correct. Try to use '=TEXT(DEC2HEX(11929);"#")'. The result is "2" and 99 zeros. Because this behaviour is cyclic I believe that fix of this should be quite simple. Steps to Reproduce: 1.Enter formula '=TEXT(DEC2HEX(11929);"#")' into a cell Actual Results: 2000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 Expected Results: 2E99 Reproducible: Always User Profile Reset: Yes Additional Info: Version: 7.4.0.3 (x64) / LibreOffice Community Build ID: f85e47c08ddd19c015c0114a68350214f7066f5a CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: pl-PL (pl_PL); UI: pl-PL Calc: threaded
Repro with Version: 7.3.5.2 / LibreOffice Community Build ID: 30(Build:2) CPU threads: 12; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb) Locale: pt-BR (pt_BR.UTF-8); UI: en-US Ubuntu package version: 1:7.3.5-0ubuntu0.22.04.1 Calc: threaded And also with Version: 7.5.0.0.alpha0+ / LibreOffice Community Build ID: 641d92a73e5b3d0e062e16ed4b42236e1a4796a5 CPU threads: 12; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb) Locale: pt-BR (pt_BR.UTF-8); UI: en-US Calc: threaded Indeed the ranges described by the OP show weird results.
This is not a bug. First of all: what is your expectation using the "TEXT(DEC2HEX)"? The TEXT(..., "#") function formats the passed number according to the numeric format string; while DEC2HEX creates a *string* representing the number in hexadecimal form. So this combination: =TEXT(DEC2HEX(<number>);"#") first converts <number> to a string, and then tries to interpret the string as a number, to be converted to another string again. Now to the conversion of strings to numbers: any user input is attempted to be treated as a *decimal* number. So, 2E99 is treated as a decimal number in exponential (E) notation = 2 * 10 ^ 99 [1]. So: 1. Hexadecimal string "1" treated as decimal, converts to number 1, and then output as "1"; 2. Hexadecimal string "10" (with value of sixteen) is treated as decimal, converts to number10 (ten!), and output as "10", but it just coincides with what you expect; 2. Hexadecimal string "2E99" treated as decimal, converts to 2 * 10 ^ 99, and output as "2000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"; 3. Hexadecimal string "1EA" cannot be interpreted as a number in any known format, and is treated as *string*, so output unmodified as "1EA". Closing NOTABUG. Use DEC2HEX by its own, without TEXT. [1] https://en.wikipedia.org/wiki/Scientific_notation#E_notation
(In reply to Mike Kaganski from comment #2) > This is not a bug. > 2. Hexadecimal string "2E99" treated as decimal, converts to 2 * 10 ^ 99, > and output as Hi Mike, thanks for your explanation. I hadn't realized that 2E99 was being treated as scientific notation. You're correct... this is NAB.
(In reply to Mike Kaganski from comment #2) > This is not a bug. > > First of all: what is your expectation using the "TEXT(DEC2HEX)"? The > TEXT(..., "#") function formats the passed number according to the numeric > format string; while DEC2HEX creates a *string* representing the number in > hexadecimal form. > (...) > > Closing NOTABUG. Use DEC2HEX by its own, without TEXT. Thank you for this detailed explanation. Looks like it was quite late since I didn't notice it myself. Now I can't remember why I used such a combination, for sure at some point there was a problem with getting the text format for the output and the TEXT function remained even when it was no longer needed. Thank you Mike (and others) again and sorry for the confusion :)