No of visitors who read this post: 1104
Category: MS Excel
Type: Question
Author: Chachajutex
No votes yet

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?

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

# Comment Id: 24532

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.

# Comment Id: 24568

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.