36

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?

6 Answers 6

29

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.

17

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.

4
  • 1
    For reference, there is also DEC2BIN() which converts to binary. For fixed width binary, use something like =TEXT(DEC2BIN(A1), "0000") Jul 28, 2015 at 14:11
  • 2
    On a German localized Excel 2013, DEC2HEX and HEX2DEC result in the error message #NAME?. With the Office Help, I could find out that the functions are called DEZINHEX and HEXINDEZ, resp. Jul 18, 2016 at 9:41
  • How do I extend =0x1234AB to =0x001234AB (I need fixed width of 8)?
    – Danijel
    Jan 27, 2017 at 8:59
  • @Danijel Use the second parameter of DEC2HEX.
    – Vincent G
    Feb 1, 2017 at 8:46
9

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

1
  • 4
    You may want to add a leading "0x" to your hex value. Use ="0x"&DEC2HEX(A1,4) to get a result that looks like like 0x1AF2.
    – User5910
    Jun 30, 2016 at 22:47
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

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.

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))
1
  • 2
    An explanation of what is going on in your code would make the answer more useful.
    – WilliamKF
    Jan 20, 2017 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.