CODEWITHSUNDEEP
excelexcel-formulaexcel-2016
Levi
Levi

Reputation: 83

Hex values in one word in Excel

How do I make Excel 2016 convert hex value in one word (two bytes) into a decimal (0xffff must convert to -1 and not to 65535)? Thanks

Upvotes: 1

Views: 4068

Answers (2)

Ovaflo
Ovaflo

Reputation: 684

Assuming you have your 16-bit (2-byte) hex value (e.g. "FFFF") in cell A1, you could do this:

=BITXOR(HEX2DEC(A1),2^15)-2^15

This will:

  1. Convert the hex value to unsigned decimal (with the intended negative range of numbers starting at 2^15, i.e. above the intended non-negative range).
  2. Invert the most significant bit (bit 15) in order to swap the intended non-negative and negative ranges (both ranges are still non-negative at this point, but now the intended negative range is below the intended non-negative range).
  3. Shift both ranges down so the intended negative range reaches its final destination below zero, and the intended non-negative range reaches its final destination at zero and above.

Examples: "0" becomes 0, "7FFF" becomes 32767, "8000" becomes -32768, and "FFFF" becomes -1.

Note: If the input value contains garbage (e.g. "0x") before or after the actual hex value, then this garbage must be removed (e.g. by doing MID(A1,3,4)) before calling HEX2DEC.

Upvotes: 4

ashleedawg
ashleedawg

Reputation: 21639

The prefix 0x (used to indicate hexadecimal notation in ) can simply be dropped.

Then Excel's HEX2DEC function can do the conversion.

So if A1 contained 0x3039, you could use this formula in B2 (or where ever) to convert it to decimal:

=HEX2DEC(MID(A1,3,99))

The MID function parameters are saying "return a string up to 99 characters, starting at the 3rd character of A1." (So, drop the first two characters)

...and to make up for your "custom -1 rule", you could add an IF statement:

=IF(LOWER(A1)="0xffff",-1,HEX2DEC(MID(A1,3,99)))

More Information:

Upvotes: 2

Related Questions