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.

Excel only has number precision up to 15 digits, your number is 16 digits long. — Nick is tired, Aug 31 '21 at 09:13
For an exact explanation you can see https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel. The IEEE 754 format used with a 64 bit representation has 53 significant bits for the "significand". The number given here only needs 51 bits however; shouldn't it be exactly converted? — Christopher Hamkins, Aug 31 '21 at 09:54

score 2 · Answer 1 · answered Aug 31 '21 at 10:07

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

answered Aug 31 '21 at 10:07

Gustav

53,498
7
29
55

Above code prints the following 1.21301732861043E+15. As Nick per Nick statement , Excel supports 16 digit precision ? @Gutstav. – Karthikeyan Vijaya kumar Aug 31 '21 at 12:23
@KarthikeyanVijayakumar: What I posted is what VBA printed. You must assign the value to a _Decimal_ (Variant) to proceed; Double doesn't have the precision needed. – Gustav Aug 31 '21 at 13:33

Tom Sharpe · Answer 2 · 2021-08-31T12:56:00.010

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

Thanks @Tom Sharpe, your information is helpful to address my issue. — Karthikeyan Vijaya kumar, Sep 01 '21 at 09:35

score 0 · Accepted Answer · answered Sep 01 '21 at 09:34

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

Hexadecimal to Decimal In Excel

3 Answers3