39

I would like a format a cell in Microsoft Excel 2007 in hexadecimal but am not finding how to do this.

Is there a built-in way to change the base from 10 to 16 for a cell?

Raystafarian
  • 21,963
  • 12
  • 64
  • 91
WilliamKF
  • 8,058

6 Answers6

35

As mentioned earlier, the formula =DEC2HEX(A1) converts to hex, and DEC2HEX(A1,8) converts to hex with 0 prefix to denote 32 bits. While adding the leading 0's makes the numbers more readable, especially if you use a fixed point font, the hex and decimal versions can get mixed up when all of the digits in the number happen to be 0-9 (e.g. 327701=50015).

An improvement is to add the "0x" prefix. There are 2 ways to do this. ="0x"&DEC2HEX(A1,8) will do the trick, but it changes the field to a text field, so it can no longer easily be used in formulas. Another method is to use a custom format. If you apply the custom format "0x"@ to the cell, then the value of the cell can still be used in an equation. Examples:


╔═══╦════════════════╦════════════╦═════════════════════════════╦═══════════════╗
║   ║       A        ║     B      ║                             ║               ║
╠═══╬════════════════╬════════════╬═════════════════════════════╬═══════════════╣
║ 1 ║                ║ Value      ║ Corresponding Formula       ║ Number Format ║
║ 2 ║ Decimal        ║ 11162790   ║ 11162790                    ║ General       ║
║ 3 ║ Hex            ║ AA54A6     ║ =DEC2HEX(B2)                ║ General       ║
║ 4 ║ leading 0's    ║ 00AA54A6   ║ =DEC2HEX(B2,8)              ║ General       ║
║ 5 ║ Text 0x prefix ║ 0x00AA54A6 ║ =DEC2HEX(B2,8)              ║ "0x"@         ║
║ 6 ║ Text 0x prefix ║ 0x00AA54A6 ║ ="0x" & DEC2HEX(B2,8)       ║ General       ║
║ 7 ║ Use B5         ║ AA54A600   ║ =DEC2HEX(HEX2DEC(B5) * 256) ║ General       ║
║ 8 ║ Use B5         ║ 0xAA54A600 ║ =DEC2HEX(HEX2DEC(B5) * 256) ║ "0x"@         ║
║ 9 ║ Try to use B6  ║ #NUM!      ║ =DEC2HEX(HEX2DEC(B6) * 256) ║ General       ║
╚═══╩════════════════╩════════════╩═════════════════════════════╩═══════════════╝

However, large numbers with varying digits can still be hard to read, so I like to put commas into decimal numbers and "_"s into hex numbers. First you need to get the upper and lower 16 bits of the number. The upper 16 bits of a 32-bit number can be retrieved with = INT( A1 / 2^16 ). Dividing by 2^16 acts like a right shift of 16 bits, and the INT removes the fractional remainder. MOD can be used to get the lower 16 bits of any 32 or 64-bit number; =MOD(A1, 2^16). The MOD effectively gets the remainder back, which is the lower 16 bits. Here is a table that shows this in action. 
╔═══╦═════════════╦═══════════════╦════════════════════════════════════╦═════════╗
║   ║      A      ║      B        ║                                    ║         ║
╠═══╬═════════════╬═══════════════╬════════════════════════════════════╬═════════╣
║ 1 ║             ║ Value         ║ Corresponding Formula              ║ Format  ║
║ 2 ║ Decimal     ║ 3,098,743,209 ║ 3098743209                         ║ #,##0   ║
║ 3 ║ Upper Bytes ║ B8B3          ║ =DEC2HEX(INT( B2/2^16 ), 4)        ║ General ║
║ 4 ║ Lower Bytes ║ 11A9          ║ =DEC2HEX(MOD( B2, 2^16 ))          ║ General ║
║ 5 ║ Full Number ║ 0xB8B3_11A9   ║ ="0x" & DEC2HEX(INT( B2/2^16 ), 4) ║ General ║
║   ║             ║               ║ & "_" & DEC2HEX(MOD(B2, 2^16), 4)  ║         ║
╚═══╩═════════════╩═══════════════╩════════════════════════════════════╩═════════╝

BTW, I have to give a big thanks to https://ozh.github.io/ascii-tables/. I used that link to create the ASCII text tables. I just copied the table from my excel spreadsheet into its Input section and it automatically created the nice unicode text layout.
OldEmbeddedGuy
  • 366
19

If you want to format a cell so that you can type in a decimal number and automatically have it displayed as a hexadecimal number, then that's not possible. You can either format the cell as text and enter hexadecimal numbers directly (but note that Excel cannot use those for calculations), or use the DEC2HEX() and HEX2DEC() functions to convert between base 10 and base 16.

Indrek
  • 24,874
9

If cell to be converted is A1 use =DEC2HEX(A1).

pnuts
  • 6,242
4

If you need to perform mathematical operations on numbers converted to hex, first convert them to decimal, perform the operation, then convert back to hex.

For example, if cell A1 has a hex number, such as would be created from this formula:

=DEC2HEX(17)

which would be displayed as 11, and cell A2 has formula:

=DEC2HEX(165)

which would be displayed as A5, and you want to add them together, then this formula would get the desired result:

=DEC2HEX(HEX2DEC(A1)+HEX2DEC(A2))

which would be displayed as B6

Stephen Rauch
  • 3,296
TestIt
  • 41
1
=REPT("0",4-LEN(DEC2HEX(B3)))&DEC2HEX(B3)

The REPT formula repeats the "0" based on the length of the hex string that is generated by converting it from a decimal, and the desired length of the HEX string. In this example I am looking to generate HEX strings of length 4.

You then concatenate the leading zeros to the actual HEX string, thereby generating the HEX value of the desired length.

Advantages:

  1. Easy coping of the formula to other worksheet. With only two cell references to a decimal value, B3, to reassign.
  2. The results of this formula are treated as HEX values by Excel. You could also prefix the "0x", but I do not find it necessary.

Note: I use this when I am copying HEX strings and they get copied over at varying lengths. I first generate a column of decimal values from the original values that I can then run this formula against.

WSimpson
  • 111
0

Here is a solution I found to make sure I can format these hex numbers into HTML format: 

SWITCH(LEN(DEC2HEX(B6)),1,"000"&DEC2HEX(B6),2,"00"&DEC2HEX(B6),3,"0"&DEC2HEX(B6),4,DEC2HEX(B6))
fixer1234
  • 28,064
Stephen Frick
  • 1