DEC2HEX - HEX2DEC Issues - Number too Long?

I have found here those UDF functions:

Please Login or Register  to view this content.

To avoid starting with A (as a negative number) you need to add 0 in the formula:

Formula:

Please Login or Register  to view this content.

Hello Zbor!

I appreciate the very quick reply! It does look like that formula works, however, it spits out a DEC value that replaces the last two number values with zeroes. It is not the 'true' DEC number.

zbor formula = 45317471814165400

Actual DEC = 45317471814165458


Do you know how to make it show the 'true' DEC number?


Also, I got this all mixed up. I need to go the other way around. I need to change the DEC - 45317471814165458 to HEX - A10000219A23D2.

Is that possible with the same formula?


Thank you.

Note that numbers that large go beyond standard double precision (~15 digits) that Excel uses to store numbers. If you need more than double precision can hold, you will need to figure out exactly how you want to do this -- which will probably depend a lot on what you are doing with these numbers.

If you need to perform calculations on these numbers, you may consider peforming all calculations and storage in VBA using a Decimal data type that can store integers up to about 29 digits (see here for limits of different data types: https://msdn.microsoft.com/en-us/lib...=vs.60%29.aspx ).

If there are not calculations needed, you may use VBA to convert from hexadecimal into a Decimal data type (not double or integer or long), then convert that Decimal data type to string and store the "numbers" in Excel as strings (though you may need to take extra measures to make sure Excel does not convert the "numbers as text" into numbers and truncate them to the limits of double precision).

I'm sure there are other strategies as well. You are dealing with very large numbers here. Large enough that you will need to pay careful attention to data type and the limits imposed on those data types by whatever programming language you choose to use.

As I indicated, some of the answer to these questions probably should be context dependent. Where are these DEC numbers coming from? How are they being used?

Am I better off just entering each into the calculator in PROGRAMMER mode and translating each individual number to HEX? Or is it possible to translate the whole list via excel?

the "best" approach might be to abandon Excel in favor of a programming language with good support for very large numbers. But, I get the impression that you are unfamiliar -- not only with Excel -- but other programming languages as well.

If it must be done in Excel, we probably need to think through how to process a very large "number stored as text" (large enough that we can never allow it to be stored as a number) and convert to a hexidecimal string. I will think on it, and see what I can think up.

I can easily translate DEC to HEX via the windows calculator - it seems odd to me that it is so easy with that program but really difficult in excel. Is there any way to replicate what the calculator is doing for a list?

As near as I can tell, MS has programmed the Windows Calculator with quad-precision (~30 digit) support. Excel, for all versions (I suppose I cannot verify the newest version) is limited to double precision. I cannot explain why MS has not added quad-precision support to Excel. Some day, when you get a chance to ask the Excel Gods, you can ask them why they did not see fit to provide support for higher precision than double precision in Excel.

As I indicated -- this is easy in a program/programming language which supports very large, very precise numbers like this.

As tedious as it might be, it might be faster to do these by hand in Windows calculator.

After further research, zbor's DHex() function will work, if we adapt it to use Decimal instead of double data type. Note this page that describes the use of Decimal data type in VBA; https://msdn.microsoft.com/en-us/lib.../gg251687.aspx

We need to input the number to the UDF as a string. Dim Nb and Nb2 as variants instead of doubles, and then the rest of the code should work normally.

Please Login or Register  to view this content.

Another way:

	A	B	C
1	45317471814165458	A10000219A23D2	B1:=sCur2Hex(A1)

Please Login or Register  to view this content.

C1 is showing the formula that goes in B1. i.e.,

=sCur2Hex(A1)

You need to put the code in the workbook and save as an xlsm file:

Adding a Macro or UDF to a Standard Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel

You're welcome.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Start a new thread -- we'll get you sorted.

	A	B	C
1	Hex	Decimal String
2	FFFFFFFFFFFFFF	72057594037927935	B2: =HexToDecStr(A2)
3	FFFFFFFFFFFFFFFFFFFFFFFF	79,228,162,514,264,337,593,543,950,335	B3: =HexToDecStr(A3, ",")
4	123456789ABCDEF	81 985 529 216 486 895	B4: =HexToDecStr(A4, " ")
5	FFFFFFFFFFFFFFFFFFFFFFFF	79228162514264337593543950335	B5: =HexToDecStr(A5)