Hexadecimal calculation

[aircoryell]

I'm trying to convert a large hexadecimal values(12digits) into decimal using the HEX2DEC function.

Using a smaller 10digit value it works fine

62FF1045BA = 4.25186E+11

However when I increase to 12 digits like below i get the following error.

62FF1045BAE9 = #NUM!

If that coversion cant be done is it possible to subtract two 12-digit hexadecimal values in Excel? Thanks in advance.

 

[DRJ]

Hi - Welcome to the board

You are limited to max 10 characters

From Excel Help

HEX2DEC(number)

Number is the hexadecimal number you want to convert. Number cannot contain more than 10 characters (40 bits).

 

[Richie(UK)]

Hi a,

As Jacob says, within Excel you are limited. However, if you use a UDF you can achieve your objective. Here are a couple of functions by Laurent Longre:

Function DHex(Nb As Double) As String

  Dim Nb2 As Double
  Dim IsNeg As Boolean
  Nb = WorksheetFunction.Round(Nb, 0)
  IsNeg = Nb< 0
  If IsNeg Then _
    Nb = 16 ^ (Int(WorksheetFunction.Log(-Nb, 16)) + 2) + Nb
  Do While Nb
    Nb2 = Int(Nb / 16)
    DHex = Mid("0123456789ABCDEF", Nb - Nb2 * 16 + 1, 1) & DHex
    Nb = Nb2
  Loop
  If Not IsNeg Then DHex = "0" & DHex
  
End Function

Function HDec(Hex As String) As Variant

  Dim I As Integer
  Dim HexLen As Integer
  Dim Code As Byte
  Dim Exp As Double
  Dim Res As Double
  HexLen = Len(Hex)
  If HexLen > 13 And Left(Hex, 1)<> "0" Then Exit Function
  Exp = 1
  For I = 0 To HexLen - 1
    Code = Asc(Mid(Hex, HexLen - I, 1))
    If Code > 64 Then Res = Res + (Code - 55) * Exp _
    Else Res = Res + (Code - 48) * Exp
    Exp = Exp * 16
  Next I
  HDec = Res
  If Left(Hex, 1) = "0" Then Exit Function
  HDec = HDec - 16 ^ HexLen

End Function

And here is how it would work with your example:

UDFs.xls
	F	G	H	I
10	62FF1045BAE9	-1.72627E+14
Sheet3

HTH

 

[Ahnold]

Don't laugh.......here is a formula based way to get a 12 digit Hex to Dec number when hex number is in A1.
=SUM(HEX2DEC(MID(A1,1,1))*2^44,HEX2DEC(MID(A1,2,1))*2^40,HEX2DEC(MID(A1,3,1))*2^36,HEX2DEC(MID(A1,4,1))*2^32,HEX2DEC(MID(A1,5,1))*2^28,HEX2DEC(MID(A1,6,1))*2^24,HEX2DEC(MID(A1,7,1))*2^20,HEX2DEC(MID(A1,8,1))*2^16,HEX2DEC(MID(A1,9,1))*2^12,HEX2DEC(MID(A1,10,1))*2^8,HEX2DEC(MID(A1,11,1))*2^4,HEX2DEC(MID(A1,12,1))*2^0)

Richie,
My formula results in a different value then yours. Do you see any errors.

 

[DRJ]

Using the windows calculator I get this

62FF1045BAE9 = 108847629187817 = 1.088E+14

From http://www.microcontroller.com/Embedded.asp?did=92

62FF1045BAE9 = 108847629187817 = 1.088E+14

 

[parry]

Hi, I wont pretend I understand Hex 2 Dec conversion but I see Laurent's function actually returns the correct answer at this point ....

HDec = Res

Im unsure what the next 2 lines achieve. Do you know Richie?
If you want the full number to be shown rather than the scientific notation then convert the answer to a string.

eg HDec = CStr(Res)

regards
Parry.

 

[Richie(UK)]

Hi,

My bad - you actually need to precede the Hex number with a zero if you are not dealing with negatives. So it should be:

UDFs.xls
	F	G	H	I
10	062FF1045BAE9	1.08848E+14
Sheet3

 

[tusharm]

Here's a variant (minimal testing) that works up to 28 decimal places. The largest value I tried was FFFFFFFFFFFFFFFFFFFFFFF which became 4,951,760,157,141,521,099,596,496,895 (as a returned string).

FFFFFFFFFFFFFFFFFFFFFFFF returns Overflow (6)

If the returned value has to be a number (i.e., cannot be a string), we are limited to 15 digits. The largest number I tested correct to the last digit is FFFFFFFFFFFF, i.e., 281,474,976,710,655. However, the function does work with larger arguments. For FFFFFFFFFFFFFFFFFFFFFFF it will return 4,951,760,157,141,520,000,000,000,000. Contrast it with the string result above.

The code:

Function TMHex2Dec(ByVal HexVal As String, _
        Optional StringOK As Boolean = False) As Variant
    Dim i As Integer, ThisRslt As Integer, Mult As Variant
    On Error GoTo ErrXIT
    Mult = CDec(1)
    For i = Len(HexVal) To 1 Step -1
        ThisRslt = CLng("&h" & Mid(HexVal, i, 1))
        TMHex2Dec = TMHex2Dec + CDec(ThisRslt * Mult)
        Mult = CDec(Mult * 16)
        Next i
    If Len(TMHex2Dec) > 15 And StringOK Then
        TMHex2Dec = Format(TMHex2Dec, "#,000")
        End If
    Exit Function
ErrXIT:
    TMHex2Dec = Err.Description & " (" & Err.Number & ")"
    End Function