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).
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.
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:And here is how it would work with your example: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
******** ******************** ************************************************************************>
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
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.
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
=
F G H I 10 062FF1045BAE9 1.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
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
Tushar Mehta (Microsoft MVP Excel 2000-present)
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office
Like this thread? Share it with others