exceloffice365excel-2010number-formattingexcel-365

How to convert big numbers between HEX and DEC in Excel without using VBA?

The Excel-Functions =HEXTODEC(hex) and =DECTOHEX(dec) work fine but only up to a value for hex of 0x7F FF FF FF FF. I need to convert numbers up to 0xFF FF FF FF FF. Beginning with 0x80 00 00 00 00 Excel sadly starts interpreting is as a signed value and converts it to a negative decimal. If I try to convert 549 755 813 888 to a HEX value I only get an error.

Has somebody an idea for a workaround? Is it possible to split the hex/dec value up in two parts, convert it both and combine it back together? At the moment I am out of ideas.

The solution shall not contain VBA. New Functions from Office365 can be used, I already used a lot of bit shifts and bitvise xor.

Thank you

Solution

  • You can use the miracle of two's complement:

    =DEC2HEX(IF(A1>=POWER(2,39),A1-POWER(2,40),A1))

    And

    =IF(HEX2DEC(A1)<0, HEX2DEC(A1)+POWER(2,40), HEX2DEC(A1))

    for the inverse.