Results 1 to 8 of 8

Hexadecimal calculation

This is a discussion on Hexadecimal calculation within the Excel Questions forums, part of the Question Forums category; I'm trying to convert a large hexadecimal values(12digits) into decimal using the HEX2DEC function. Using a smaller 10digit value it ...

  1. #1
    New Member
    Join Date
    Apr 2004
    Posts
    6

    Default Hexadecimal calculation

    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.

  2. #2
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    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).

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329

    Default Re: Hexadecimal calculation

    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:
    Code:
    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:


    ******** ******************** ************************************************************************>
    Microsoft Excel - UDFs.xls___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    F
    G
    H
    I
    10
    62FF1045BAE9-1.72627E+14**
    Sheet3*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    HTH
    Richie

  4. #4
    Board Regular Ahnold's Avatar
    Join Date
    Feb 2004
    Location
    Baltimore, MD
    Posts
    636

    Default Re: Hexadecimal calculation

    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,H EX2DEC(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.

  5. #5
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    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

  6. #6
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default Re: Hexadecimal calculation

    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.

  7. #7
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329

    Default Re: Hexadecimal calculation

    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:


    ******** ******************** ************************************************************************>
    Microsoft Excel - UDFs.xls___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    F
    G
    H
    I
    10
    062FF1045BAE91.08848E+14**
    Sheet3*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Richie

  8. #8
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default Re: Hexadecimal calculation

    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:
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com