How do I convert a 64bit number to hexadecimal in excel?

Question

I'm trying DEC2HEX(1000000000050000000) but it comes out as #NUM! as the number is too large for this function.

Is there another function I could use to turn this number into hexadecimal?

Answer 1

If you want to convert a decimal number to a 64 bit hex string, assuming that 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 decimal value of 18,446,744,073,709,500,000 or hex value of 0xfffffffffffff800.

Bonus:

To convert from hex string to decimal, assuming that the 64bit hex string is in cell A1 and contains 16-characters then 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 has a 0x then you can use the following:

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

Answer 2

I found a simple solution for converting HEX to DEC and vice versa without the limits of characters.

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

• Case 1: I want to convert hex value "3C" to decimal, the formula is DECIMAL(3C, 16).
• Case 2: I want to convert 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 number value "1500" to hexadecimal, the formula is BASE(1500, 16)

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