Hexadecimal to Decimal In Excel

Question

I am trying to convert a Hexadecimal value to a decimal one in excel with this reference.

Used parameters and formulas:

Input Hex value : 0x044F3B9AFA6C80 and expected output : 1213017328610432

• Method1: Applying excel formula

'=HEX2DEC(RIGHT(D3164,10))+HEX2DEC(MID(D3164,3,4))*POWER(16,10)'

Actual output : 1213017328610430

• Method2: Using VBA macro:

    ' 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

Actual output : 1213017328610430

When I try to convert this value with online conversion tool or with python script, it covert expected decimal value.

Please any hint on issue will be more helpful.

Answer 1

Thank you very much everyone. Finally I can able to covert hex to dec value with more then 16 digit. Excel only shows 16 digits in their each cells, so I have converted number into string helps me to present expected value in the cells.

Please find the final VBA code for any further reference.

' 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 String

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

End Function

Answer 2

You can chop it into two parts:

HexText = "0x044F3B9AFA6C80"
HexHi = "&H" & Mid(HexText, 3, 8)
HexLo = "&H" & Mid(HexText, 11)

? CDec(HexHi) * 2 ^ 24 + CDec(HexLo)
 1213017328610432 

Answer 3

I think the short answer is that you can't get more than 15 digits of precision out of a Double data type, as noted in the comments. The only way to get the correct result is to use a Decimal data type, and you can only do this in VBA. In VBA, you can't declare a Decimal type directly, but have to create it using Cdec and store it in a variant see documentation:

Option Explicit

Sub test()
    Dim dubl As Double
    Dim htext As String
    Dim var As Variant
    
    htext = "&H044F3B9AFA6C80"
      
    dubl = CDec(htext)
    var = CDec(htext)
    
    Debug.Print (dubl)
    Debug.Print (var)
End Sub

Result:

 1.21301732861043E+15 
 1213017328610432