How to convert one number system to another number system in Excel

Asked By 10 points N/A Posted on -
qa-featured

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?

SHARE
Best Answer by zicooo82
Answered By 0 points N/A #115842

How to convert one number system to another number system in Excel

qa-featured

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.

Best Answer
Best Answer
Answered By 0 points N/A #115844

How to convert one number system to another number system in Excel

qa-featured
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.

Related Questions