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