Microsoft Excel - Hex to decimal in excel. - Asked By Shaan Chit on 11-Jan-09 10:57 PM

Hi guys

 

I am trying to convert a hex t decimal and I am using HEX2DEC(“ ”) formula, but I am getting a #num error.

 

I tried another calculator online to convert it and that works fine.

 

I guess that there is some formatting issue.

 

The number is 3030303236303030.

 

Any tip would be greatly appreciated.

 

Thanks in advance.

 

Re :: Hex to Decimal in Excel - Shailendrasinh Parmar replied to Shaan Chit on 11-Jan-09 11:18 PM


Hello, see the following article

HEX2DEC

Converts a hexadecimal number to decimal.

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program.
Syntax

HEX2DEC(number)

Number is the hexadecimal number you want to convert. Number cannot contain more than 10 characters (40 bits). The most significant bit of number is the sign bit. The remaining 39 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Remark

If number is not a valid hexadecimal number, HEX2DEC returns the #NUM! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.


Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

Formula Description (Result)
=HEX2DEC("A5") Converts hexadecimal A5 to decimal (165)
=HEX2DEC("FFFFFFFF5B") Converts hexadecimal FFFFFFFF5B to decimal (-165)
=HEX2DEC("3DA408B9") Converts hexadecimal 3DA408B9 to decimal (1034160313)


--------------------------------------------------------------------------------

You can import a list of decimal numbers into excel and then create a list of convertor forumulas.
The hex2dec will convert any length HEX number, not just two digits.

You could also create a macro in excel which could do the same thing.

Hope this helps.

try this code - C_A P replied to Shaan Chit on 12-Jan-09 01:36 AM


Public Function GetLargeIntegerFromHex( _
      ByVal HexText As String _
   ) As String
   
   Dim Base2 As String
   Dim Result As String
   Dim Index As Long
   Dim Bit As Long
   Dim Char As Byte
   
   Base2 = "1"
   Result = "0"
   
   HexText = UCase(HexText)
   
   For Index = Len(HexText) To 1 Step -1
      Char = CLng("&H" & Mid(HexText, Index, 1))
      For Bit = 1 To 4
         If IsBitSet(Char, Bit) Then Result = AddString(Result, Base2)
         Base2 = AddString(Base2, Base2)
      Next Bit
   Next Index
   
   GetLargeIntegerFromHex = Result

End Function

Public Function AddString( _
      ByVal Value1 As String, _
      ByVal Value2 As String _
   ) As String

   Dim ResultIndex As Long
   Dim OperandIndex As Long
   Dim Result As String
   Dim Operand As String
   Dim Sum As Long
   Dim
Carry As Long
   
   If Len(Value1) > Len(Value2) Then
      Result = Value1
      Operand = Value2
   Else
      Result = Value2
      Operand = Value1
   End If
   
   ResultIndex = Len(Result)
   OperandIndex = Len(Operand)
   Do While OperandIndex > 0
      Sum = CLng(Mid(Result, ResultIndex, 1)) + CLng(Mid(Operand, OperandIndex, 1)) + Carry
      If Sum > 9 Then
         Carry = 1
         Mid(Result, ResultIndex, 1) = Sum - 10
      Else
         Carry = 0
         Mid(Result, ResultIndex, 1) = Sum
      End If
      ResultIndex = ResultIndex - 1
      OperandIndex = OperandIndex - 1
   Loop
   If Carry = 1 Then Result = "1" & Result
   
   AddString = Result

End Function

Public Function IsBitSet( _
      ByVal Value As Variant, _
      ByVal Position As Long _
   ) As Boolean
   
   Select Case VarType(Value)
      Case vbLong, vbInteger, vbByte
         IsBitSet = (CLng(Value) And (2 ^ (Position - 1))) > 0
   End Select

End Function

sample code - C_A P replied to Shaan Chit on 12-Jan-09 01:37 AM

			

Option Explicit

Public Function HexToDec(Hex As String) As Double

Dim i As Long
Dim j As Variant
Dim k As Long
Dim n As Long
Dim HexArray() As Double

n = Len(Hex)
k = -1
ReDim HexArray(1 To n)
For i = n To 1 Step -1
j = Mid(Hex, i, 1)
k = k + 1
Select Case j
Case 0 To 9
HexArray(i) = j * 16 ^ (k)
Case Is = "A"
HexArray(i) = 10 * 16 ^ (k)
Case Is = "B"
HexArray(i) = 11 * 16 ^ (k)
Case Is = "C"
HexArray(i) = 12 * 16 ^ (k)
Case Is = "D"
HexArray(i) = 13 * 16 ^ (k)
Case Is = "E"
HexArray(i) = 14 * 16 ^ (k)
Case Is = "F"
HexArray(i) = 15 * 16 ^ (k)
End Select
Next i
HexToDec = Application.WorksheetFunction.Sum(HexArray)

End Function


How to use:

  1. Open Excel.
  2. Alt + F11 to open the VBE.
  3. Insert | Module.
  4. Paste the code from above in the Code Window that opens up.
  5. Close the VBE (Alt + Q or press the X in the top right corner).
 

Test the code:

  1. Option 1: In a worksheet cell input = HexToDec(#).
  2. Option 2: In a VBA macro input MyNum = HexToDec(#).
  3. Just replace # with your number.
 
thanks mate - Shaan Chit replied to C_A P on 12-Jan-09 04:37 PM
end of post
thanks mate - Asked By Shaan Chit on 12-Jan-09 04:38 PM
end of post
thanks mate - Shaan Chit replied to Shailendrasinh Parmar on 12-Jan-09 04:38 PM
end of post
david sab replied to Shaan Chit on 01-Apr-10 02:30 PM
Here is an example of the online tool Shann was talking about: hex to decimal converter
David