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

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

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
 G10 =

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

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

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.

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
 G10 =

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.

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

#### Posting Permissions

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