How to convert a 64-bit hexadecimal number to excel?

Question

I am trying DEC2HEX(1000000000050000000) , but it looks like #NUM! since the number is too large for this function.

Is there any other function that I could use to turn this number into hexadecimal?

Answer 1

There is a free add-in that will handle this: Xnumbers

It seems to work fine:

 =cvDecBase("1000000000050000000",16) --> DE0B6B3AA5EF080 

Answer 2

If you want to convert a decimal number to a 64-bit hexadecimal string, assuming the decimal number is in cell A1, you can use the following:

 =CONCATENATE(DEC2HEX(A1/2^32),DEC2HEX(MOD(A1,2^32),8)) 

This will work up to the decimal value 18,446,744,073,709,500,000 or the hexadecimal value 0xffffffffffffff800.

Bonus:

To convert a hexadecimal string to decimal, provided that the 64-bit hexadecimal string is in cell A1 and contains 16 characters, you can use the following:

 =HEX2DEC(LEFT(A1,8))*2^32+HEX2DEC(RIGHT(A1,8)) 

You can adjust the number of characters in the LEFT (text, [num_chars]) to better suit your needs.

If your hex string is 0x, you can use the following:

 =HEX2DEC(MID(A1,3,8))*2^32+HEX2DEC(RIGHT(A1,8)) 

Answer 3

The DEC2HEX function has a limit of 549,755,813,887 , try this formula, it works for numbers up to 281,474,976,710,655.

=DEC2HEX(A7/(16^9),3)&DEC2HEX(MOD(A7,16^9),9)

Answer 4

I found a simple solution to convert HEX to DEC and vice versa without character restrictions.

HEX to DEC : use DECIMAL (enter cell number or coordinates, enter base number)

• Case 1: I want to convert the hexadecimal value "3C" to decimal, the formula is DECIMAL (3C, 16).
• Case 2: I want to convert the binary value "1001" to decimal, the formula is DECIMAL (1001, 2).

DEC to HEX : use BASE (input number or cell coordinates, output base number)

• Case 1: I want to convert the numeric value "1500" to hexadecimal, the formula is BASE (1500, 16)

• Case 2: I want to convert the numeric value "1500" to binary, the formula is BASE (1500, 2)