Asked By
chachajutex
10 points
N/A
Posted on - 08/17/2011
I am using Microsoft Excel 2000. I faced a problem. It’s a conversation from one number system to another number system. Suppose, I have a binary number like this 1100100.
I want to convert this number to a decimal number.
Is there any Excel formula to convert one number system to another number system?
How to convert one number system to another number system in Excel
Yes, there are some built in functions in the Excel by which you can easily convert one form of number system to another form some of the functions are as follows
==> =BIN2HEX
==> =BIN2OCT
==> =BIN2DEC
==> = DEC2BIN
==> =DEC2HEX
etc. For example if you want to convert a binary number to octal number you can write the following in an Excel cell:
=BIN2OCT(1011,3)
The above command will convert the binary number 1011 to octal numbers having three characters. The sign '=' before the function name tells the application that a builtin function is going to used. So please do not ignore the '+' sign before the function name.
Answered By
zicooo82
0 points
N/A
#115844
How to convert one number system to another number system in Excel
1. Open Excel.
2. Press ALT+F11.
3. Right Click on This workbook and choose insert module.
4. Copy and paste the following codes into the module:
Function BinToDec(D As String) As String
Dim N As Long
Dim Res As Long
For N = Len(D) To 1 Step -1
Res = Res + ((2 ^ (Len(D) – N)) * CLng(Mid(D, N, 1)))
Next N
BinToDec = Str(Res)
End Function
Function DecToBin(D As String) As String
Dim N As Long
Dim Res As String
For N = 31 To 1 Step -1
Res = Res & IIf(CLng(D) And 2 ^ (N – 1), "1", "0")
Next N
N = InStr(1, Res, "1")
DecToBin = Mid(Res, IIf(N > 0, N, Len(Res)))
End Function
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
Option Explicit
Public Function DecToHex(Dec As Double) As String
Dim i As Long
Dim n As Long
Dim PlaceValHex As Long
Dim Hex(1 To 256) As String
Dim HexTemp As String
Dim Divisor As Long
Dec = Int(Dec)
For i = 256 To 2 Step -1
If Dec >= 16 ^ (i – 1) And Dec > 15 Then
PlaceValHex = Int(Dec / (16 ^ (i – 1)))
Dec = Dec – (16 ^ (i – 1)) * PlaceValHex
Select Case PlaceValHex
Case 0 To 9
Hex(i) = CStr(PlaceValHex)
Case Is = 10
Hex(i) = "A"
Case Is = 11
Hex(i) = "B"
Case Is = 12
Hex(i) = "C"
Case Is = 13
Hex(i) = "D"
Case Is = 14
Hex(i) = "E"
Case Is = 15
Hex(i) = "F"
End Select
Else
Hex(i) = "0"
End If
Next i
PlaceValHex = Dec
Select Case PlaceValHex
Case 0 To 9
Hex(1) = CStr(PlaceValHex)
Case Is = 10
Hex(1) = "A"
Case Is = 11
Hex(1) = "B"
Case Is = 12
Hex(1) = "C"
Case Is = 13
Hex(1) = "D"
Case Is = 14
Hex(1) = "E"
Case Is = 15
Hex(1) = "F"
End Select
For i = 256 To 1 Step -1
If Hex(i) = "0" Then
Else
n = i
Exit For
End If
Next i
For i = n To 1 Step -1
HexTemp = HexTemp & Hex(i)
Next i
DecToHex = HexTemp
End Function
5. Press ALT+F11
6. Choose user defined from functions category pop-up list.
7. You will find 4 new functions helps you to convert from binary to decimal and vice versa, also from hex to dec and vice versa.
8. You can save it as Excel Add-in and attach to file in the Excel add-in to make it part of your Excel.
