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

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?

• Status: Open
• Question Views: 2175
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by zicooo82

Go To Solution

Answered By goldenbrown 0 points N/A #115842

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.

About goldenbrown

Questions
0

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 08/17/2011
• Question Category: MS Excel

Best Answer

Best Answer

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.

About zicooo82

Questions
0

Answers
11

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 08/18/2011
• Question Category: MS Excel