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

## Re :: Hex to Decimal in Excel - SP 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?

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)
Next Bit
Next Index

GetLargeIntegerFromHex = Result

End Function

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

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: Open Excel. Alt + F11 to open the VBE. Insert | Module. Paste the code from above in the Code Window that opens up. Close the VBE (Alt + Q or press the X in the top right corner). Test the code: Option 1: In a worksheet cell input = HexToDec(#). Option 2: In a VBA macro input MyNum = HexToDec(#). Just replace # with your number.
end of post
end of post
##### thanks mate - Shaan Chit replied to SP 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