14 digit Hex to decimal in Excel

I have 14 digit Hexadecimal numbers in Excel which I want to convert to a decimal number.

For example in C2 the number is 0438E96A095180 and that has to be 1188475064373632 in decimal.

I have tried a module in VBA but that is not working:

' 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 

With that I get the decimal number 1188475064373630 instead of 1188475064373632.

What am I doing wrong?

1 Answer

You will need to return the value as a string, at least where the result has more than 15 digit precision.

eg:

Option Explicit ' Convert hex to decimal ' In: Hex in string format ' Out: Decimal in string format Public Function HexadecimalToDecimal(HexValue As String) As String ' If hex starts with 0x, remove it to represent Hex that VBA will understand Dim ModifiedHexValue As String ModifiedHexValue = "&H" & Replace(HexValue, "0x", "") HexadecimalToDecimal = CDec(ModifiedHexValue) End Function 

I will leave it to you to test the length and decide if you want to return a string or a number; and you will note that I modified your &H adding routine a bit.