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

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?

Solution 1:

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.

Solution 2:

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.