Convert any length signed hexadecimal number to signed decimal number (Excel)

user_5447681 0
Viewed 10762

Question

When faced with signed hexadecimal numbers of unknown length, how can one use Excel formulas to easily convert those hexadecimal numbers to decimal numbers?

Example

Hex
---
00
FF
FE
FD
0A
0B
decimal excel-formula hex excel
2 Answers

Use this deeply nested formula:

=HEX2DEC(N)-IF(ISERR(FIND(LEFT(IF(ISEVEN(LEN(N)),N,CONCAT(0,N))),"01234567")),16^LEN(IF(ISEVEN(LEN(N)),N,CONCAT(0,N))),0)

where N is a cell containing hexadecimal data.

This formula becomes more readable when expanded:

=HEX2DEC(N) -
 /* check if sign bit is present in leftmost nibble, padding to an even number of digits if necessary */
 IF( ISERR( FIND( LEFT( IF( ISEVEN(LEN(N))
                          , N
                          , CONCAT(0,N)
                          )
                      )
                , "01234567"
                )
          )
   /* offset if sign bit is present */
   , 16^LEN( IF( ISEVEN(LEN(N))
               , N
               , CONCAT(0,N)
               )
            )
   /* do not offset if sign bit is absent */
   , 0
   )

and may be read as "First, convert the hexadecimal value to an unsigned decimal value. Then offset the unsigned decimal value if the leftmost nibble of the data contains a sign bit; else do not offset."

Example Conversion

Hex  | Dec
-----|----
00   |   0
FF   |  -1
FE   |  -2
FD   |  -3
0A   |  10
0B   |  11
user_54476810

Let the A1 cell contain a 1 byte hexadecimal string of any case.

To get the 2's complement decimal value of this string, use the following:

=HEX2DEC(A1)-IF(HEX2DEC(A1) > 127, 256, 0)

For an arbitrary length of bytes, use the following:

=HEX2DEC(A1) - IF(HEX2DEC(A1) > POWER(2, 4*LEN(A1))/2 - 1, POWER(2, 4*LEN(A1)), 0)
user_12503190
Related