Formula to convert Hex to Decimal

Dan,

Here's a brute-force solution. There may be more elegant ones out there.



DEC formula is: =IF(ISERROR(LOOKUP(MID(A2, 1, 1), LOOKUP TBL :: A$1:A$16, LOOKUP TBL :: B$1:B$16) * 16^3+LOOKUP(MID(A2, 2, 1), LOOKUP TBL :: A$1:A$16, LOOKUP TBL :: B$1:B$16) * 16^2+LOOKUP(MID(A2, 3, 1), LOOKUP TBL :: A$1:A$16, LOOKUP TBL :: B$1:B$16) * 16+LOOKUP(MID(A2, 4, 1), LOOKUP TBL :: A$1:A$16, LOOKUP TBL :: B$1:B$16)),"", LOOKUP(MID(A2, 1, 1), LOOKUP TBL :: A$1:A$16, LOOKUP TBL :: B$1:B$16) * 16^3+LOOKUP(MID(A2, 2, 1), LOOKUP TBL :: A$1:A$16, LOOKUP TBL :: B$1:B$16) * 16^2+LOOKUP(MID(A2, 3, 1), LOOKUP TBL :: A$1:A$16, LOOKUP TBL :: B$1:B$16) * 16+LOOKUP(MID(A2, 4, 1), LOOKUP TBL :: A$1:A$16, LOOKUP TBL :: B$1:B$16))

Be sure to Format Col A of both tables to Text.

Regards,

Jerry

Storing the string 0123456789ABCDEF in A1,

If the decimal number is in column B,

=MID($A$1,1+INT(INT(B/16777216)/16),1)&MID($A$1,1+MOD(INT(B/16777216),16),1)&MID ($A$1,1+INT(INT(MOD(B,16777216)/65536)/16),1)&MID($A$1,1+MOD(INT(MOD(B,16777216) /65536),16),1)&MID($A$1,1+INT(INT(MOD(B,65536)/256)/16),1)&MID($A$1,1+MOD(INT(MO D(B,65536)/256),16),1)&MID($A$1,1+INT(MOD(B,256)/16),1)&MID($A$1,1+MOD(MOD(B,256 ),16),1)
converts it in hexadecimal.

If the hexadecimal number is in column C,

=IF(LEN($C)=8,((SEARCH(MID($C,1,1),$A$1)-1)*16+(SEARCH(MID($C,2,1),$A$1)-1))*256 ^3+((SEARCH(MID($C,3,1),$A$1)-1)*16+(SEARCH(MID($C,4,1),$A$1)-1))*256^2+((SEARCH (MID($C,5,1),$A$1)-1)*16+(SEARCH(MID($C,6,1),$A$1)-1))*256+(SEARCH(MID($C,7,1),$ A$1)-1)*16+(SEARCH(MID($C,8,1),$A$1)-1),123)
converts it in decimal.

Yvan KOENIG (from FRANCE lundi 24 novembre 2008 11:54:38)

Hello

My original post started with this sentence:

Storing the string 0123456789ABCDEF in A1,

I'm not accustomed to write only to fill space.
Enter the string in the cell A1 if you really want to use the given formula.
This one make use of this cell's contents.

Yvan KOENIG (from FRANCE mardi 25 novembre 2008 16:16:36)