How can I convert a hex number to a decimal number in Excel?

Question

I have a cell containing this value:

0x00006cd2c0306953

and I want to convert it to a number.

I tried:

=HEX2DEC(C8)

where C8 is the cell containing my hex value.

I get a #NUM error.

What am I doing wrong?

according to me C is not capital it may be small . so try 'c8' — Sandy8086, Sep 10 '12 at 07:54
@Sandy8086: Excel doesn't care about the case of the cell indeces — Nathan Fellman, Sep 10 '12 at 11:54

score 11 · Answer 1 · edited Aug 09 '16 at 02:34

11

You could simply use:

HEX2DEC(right(A1,(len(A1)-2)))

len(A1) gives you the length of 0x string. Exclude the length of 0x, the remaining string is the hex number. Use Excel function to get the dec.

edited Aug 09 '16 at 02:34

Hewbot

2,019
12
20

answered Aug 09 '16 at 01:09

vegeta

127
1
2

2

Have you tried this? Have you read anything on this page? It has been stated three times that `HEX2DEC` accepts a maximum of ten hex digits. The string in the question, `0x00006cd2c0306953`, is 18 characters long. Your solution evaluates `RIGHT(A1,(LEN(A1)-2))`; 18−2 is 16, so `RIGHT(…)` returns `00006cd2c0306953`, which is 16 digits long. Even if you strip off the leading zeroes, you have 12 digits, which is *still* too many. If you can get this to work, please describe your configuration. (I.e., what version of Excel are you using? And did you do anything tricky to get this to work?) – Scott - Слава Україні Aug 20 '16 at 02:31

score 7 · Answer 2 · edited Nov 22 '17 at 03:05

As TwiterZX indicated, Hex2Dec's input is limited to 10 characters and 6cd2c0306953 is 12 characters. So that won't work but let's roll our own function for that. Using VBA, add a Module and use the following code (may need to be adjusted based on your needs)

' Force explicit declaration of variables
Option Explicit


' Convert hex to decimal
' In:   Hex in string format
' Out:  Double
Public Function HexadecimalToDecimal(HexValue As String) As Double

    ' If hex starts with 0x, replace it with &H to represent Hex that VBA will understand
    Dim ModifiedHexValue As String
    ModifiedHexValue = Replace(HexValue, "0x", "&H")

    HexadecimalToDecimal = CDec(ModifiedHexValue)

End Function

In Excel, let's say cell A1 contains 0x00006cd2c0306953, A2's formula of =HexadecimalToDecimal(A1) will result in 1.19652E+14. Format the column to a number with zero decimals and the result will be 119652423330131.

score 5 · Answer 3 · edited Dec 16 '14 at 13:29

HEX2DEC is limited to 10 characters, but that doesn't mean we can't use it. Simply use it several times, to convert 10 characters at a time and apply the appropriate power of 2 to each use.

= HEX2DEC(RIGHT(C8,10))+HEX2DEC(MID(C8,3,5))*POWER(16,10)

[Disclaimer: Untested at the moment]

Later: I'm now at a spreadsheet, ready to test. Change the 3,5 in MID to 3,6. Hmm.. Still not right.

Turns out that the HEX2DEC is working on signed hex values, so the first term ends up being negative. Not sure why, but here is the fixed version that adds 2^40 (or 16^10, as we're working in hex) to fix:

= HEX2DEC(RIGHT(C8,10))+POWER(16,10) + HEX2DEC(MID(C8,3,6))*POWER(16,10)

However, that only works if the RIGHT(C8,10) happens to be negative. Here's my general solution:

= HEX2DEC(RIGHT(C8,10))+IF(HEX2DEC(RIGHT(C8,10))<0,POWER(16,10),0) + HEX2DEC(MID(C8,3,6))*POWER(16,10)

Ugggh.

Only the 40th bit is the sign bit, so you can just split the 64-bit value to two 32-bit values and it'll always be positive. Otherwise pad a zero to the hex string if it's shorter than 37 bits — phuclv, Aug 04 '15 at 08:21

score 3 · Answer 4 · edited Jul 24 '13 at 15:19

3

Try the following:

=HEX2DEC(RIGHT(D93;8))+HEX2DEC(LEFT(D93;LEN(D93)-8))*POWER(2;32)

edited Jul 24 '13 at 15:19

Excellll

12,627
11
51
78

answered Jul 24 '13 at 14:59

Stas

31
1

why don't use `2^32`? – phuclv Aug 04 '15 at 08:19

score 2 · Answer 5 · answered Sep 10 '12 at 10:22

One dirty way to perform this convertion, without using a function (see this excel forum thread for that) is to use this formula to compute the value of each character in the string, then sum those up. This obviously involves using temporary cells to decompose the number:

=HEX2DEC(LEFT(RIGHT(A$1,ROW()),1))*POWER(16,ROW()-1)

Assuming you place these temp cells on rows 1 to 16, this works by extracting each character, starting from the right, converting that to a value, then applying the relevant power of 16. Sum up all 16 cells in order to get your value.

IMHO `16^(ROW() - 1)` is much cleaner and easier to read than `POWER()` — phuclv, Aug 04 '15 at 08:21

score 1 · Answer 6 · edited Sep 17 '20 at 17:05

1

Long formula but it is working for 64-HEX characters:

=HEX2DEC(MID(A24,1,8))*2^512 **(*4)** +HEX2DEC(MID(A24,9,8))*2^512 **(*2)** +HEX2DEC(MID(A24,17,8))*2^512+HEX2DEC(MID(A24,25,8))*2^256+HEX2DEC(MID(A24,33,8))*2^128+HEX2DEC(MID(A24,41,8))*2^64+HEX2DEC(MID(A24,49,8))*2^32+HEX2DEC(MID(A24,57,8))

please note:

(*4) = *4 (remove brackets)

    and:

(*2) = *2 (remove brackets)

also note: all 64 character must be present like the following example:

0000000000000000000000000000000000000000000000000000000000000fd1

Cheers

edited Sep 17 '20 at 17:05

Nathan Fellman

9,372
20
62
82

answered Sep 17 '20 at 13:15

universe33

11
2

f000000000000000000000000000000000000000000000000000000000000000 = 2.16E+164 ff00000000000000000000000000000000000000000000000000000000000000 = 2.29E+164 fff0000000000000000000000000000000000000000000000000000000000000 = 2.30E+164 ffff000000000000000000000000000000000000000000000000000000000000 = 2.30E+164 fffff00000000000000000000000000000000000000000000000000000000000 = 2.30E+164 ffffff0000000000000000000000000000000000000000000000000000000000 = 2.30E+164 fffffff000000000000000000000000000000000000000000000000000000000 = 2.30E+164 – universe33 Sep 17 '20 at 13:48

score 0 · Answer 7 · answered Oct 06 '17 at 06:53

0

Make sure your column which has HEX numbers is not having 0x. The HEX number should be C8, not 0xC8. Hope it helps.

answered Oct 06 '17 at 06:53

user2794645

1

score 0 · Answer 8 · answered Nov 21 '17 at 23:39

HEX2DEC fails if there are any leading or trailing non-hex (ABCDEF0123456789) characters. Spaces are a real landmine because they are not visible. Often hex numbers, represented as strings, in log files contain leading and/or trailing spaces. I find that "=HEX2DEC(TRIM(A1)) will usually fix this problem. However, something like "=HEX2DEC(RIGHT(TRIM(A1), 10), may be necessary due to the 10 character limitation of HEX2DEC.

score -3 · Answer 9 · answered Sep 10 '12 at 10:54

-3

This function has changed try using

=HEXDEC(C8)

so without number 2 between HEX & DEC

answered Sep 10 '12 at 10:54

Riad Krim

163
4

2

This doesn't work at all – Nathan Fellman Sep 10 '12 at 10:56
I think this function is language dependent, what's the main language of your excel app, and your version..? – Riad Krim Sep 10 '12 at 11:01
1

Office 2010 in English – Nathan Fellman Sep 10 '12 at 11:55
4

HEX2DEC (or HEXDEC) function is limited to 40 bits (10 characters), and your number is 12 characters http://office.microsoft.com/en-us/excel-help/hex2dec-function-HA102753000.aspx – Riad Krim Sep 10 '12 at 12:34
How does this language dependent? There's not even a function separator like `,` or `;` here – phuclv Aug 04 '15 at 08:17

How can I convert a hex number to a decimal number in Excel?

9 Answers9

Linked