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

Question

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

Answer 1

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.

Answer 2

I can't improve on @Jeroen's answer to this specific question but it is worth mentioning that if you can use Excel 365 functions (actually from Excel 2013 onwards) you can use the Base function instead of Dec2Hex for numbers up to 2^53-1:

=BASE(A1,16)

As far as I know there is no inverse of the Base function, but you could knock something together like this:

=LET(L,LEN(B1),seq,SEQUENCE(L),SUM((FIND(MID(B1,seq,1),"0123456789ABCDEF")-1)*16^(L-seq)))