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

Asked By 10 points N/A Posted on -

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
Answered By 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.

Answered By 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.