decimal to binary conversion

  • Thread starter Thread starter Guest
  • Start date Start date
Excel can't do it unless you implement a user defined function to calculate
it. You can use the windows calculator to convert, it can handle decimal
numbers up to 18,446,744,073,709,551,615
 
You could break up the value into pieces:

=TEXT(DEC2BIN(INT(A1/512)),REPT("0",9))&TEXT(DEC2BIN(MOD(A1,512)),REPT("0",9))

This'll work until =512^2-1 (or 262,143).

Then you'd have to break it up more.
 
How do I convert a decimal number >511 to binary?
Click to expand...

You can use this little beauty from Harlan Grove:

========================

Pick your number apart into powers of 512. For numbers from 0 to -1+2^36,

=DEC2BIN(INT(x/2^27),9)&DEC2BIN(INT(MOD(x,2^27)/2^18),9)
&DEC2BIN(INT(MOD(x,2^18)/2^9),9)&DEC2BIN(MOD(x,2^9),9)

==============================



Or you can use a UDF. Here is a general function to convert any base to any
other base in the range stated in the UDF; and also handle decimal places:

================================
Function BaseConvert(Num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld
'Handles from base 2 to base 62 by differentiating small and capital letters

Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String

DecSep = Application.International(xlDecimalSeparator)

On Error GoTo HANDLER

If FromBase > 62 Or ToBase > 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If

If InStr(1, Num, "E") And FromBase = 10 Then
Num = CDec(Num)
End If

'Convert to Base 10
LDI = InStr(1, Num, DecSep) - 2
If LDI = -2 Then LDI = Len(Num) - 1

j = LDI

Temp = Replace(Num, DecSep, "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 >= FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0

ReDim Digits(LDI)

For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i

Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)

If r <> 0 Then
Digits(0) = DecSep
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If

BaseConvert = Temp & Join(Digits, "")

Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & Num)

End Function
======================


--ron
 
hello can you help fast ? tonight /
How do I convert a decimal number >23.125 to binary?
 
hello can you help fast ? tonight /
How do I convert a decimal number >23.125 to binary?
Click to expand...

''''''''''''''''''
'by Rick Rothstein
''''''''''''''''''
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As
Variant) As String
' The DecimalIn argument is limited to 79228162514264337593543950266
' (approximately 96-bits) - large numerical values must be entered
' as a String value to prevent conversion to scientific notation.

DecToBin = "": DecimalIn = CDec(DecimalIn)
Do While DecimalIn <> 0
DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) &
DecToBin
DecimalIn = Int(DecimalIn / 2)
Loop
If Not IsMissing(NumberOfBits) Then
If Len(DecToBin) > NumberOfBits Then
DecToBin = "Error - Number too large for bit size"
Else
DecToBin = Right$(String$(NumberOfBits, "0") & DecToBin,
NumberOfBits)
End If
End If
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
When I try to implement this, it only converts the integer part, not
the decimal part.
Click to expand...

I seem to recall something about this, now that you mention it. I've
never used/tested it myself, though, to be honest!
I usually use the BAseChange function in xNumbers (do a web search
for this free add-in), for stuff like that.
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
hello can you help fast ? tonight /
How do I convert a decimal number >23.125 to binary?
Click to expand...

What do you mean by "binary"?

Do you mean 10111.001 for 23.125?

If so, what do you want for 24.05?

Ostensibly, it is 11000.000011001100(1100)... (repeating the last 4 bits).
0.05 cannot be represented exactly in binary.

Or do you really mean that you want the 64-bit binary floating-point
representation?

If so, hex representation might be best; for example, &h40380CCC,CCCCCCCD
for 23.05.
 
Back
Top