Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
according to me C is not capital it may be small . so try 'c8' –  Sandy8086 Sep 10 '12 at 7:54
4  
@Sandy8086: Excel doesn't care about the case of the cell indeces –  Nathan Fellman Sep 10 '12 at 11:54
add comment

5 Answers 5

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

This function has changed try using

=HEXDEC(C8)

so without number 2 between HEX & DEC

share|improve this answer
1  
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..? –  TwiterZX Sep 10 '12 at 11:01
    
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 office.microsoft.com/en-us/excel-help/… –  TwiterZX Sep 10 '12 at 12:34
add comment

Try the following:

=HEX2DEC(RIGHT(D93;8))+HEX2DEC(LEFT(D93;LEN(D93)-8))*POWER(2;32)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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