How do I get DEC2BIN function to display greater than 9 character.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

DEC2BIN function stops when it tries to display 10 or more characters
(conversion of decimal number 512 or greater). Is there a way to allow it to
do this? I'm looking for 12 or more characters of display.
 
Hi
try
=SUMPRODUCT(INT(MOD(A1/2^(COLUMN(1:1)-1),2))*10^(COLUMN(1:1)-1))

Maximum of 15 digits
 
=DEC2BIN(INT($B2/256),8)&DEC2BIN(MOD($B2,256),8)

gets up to 65,535

regards

JohnI
 
Hi
32768 is the max my formula could calculate as I store the returning
value as number (and here I reach the max of 15 significant digits).
You on the opposite use Text values
 
John,

I suppose you could reliably get to 262,143 (2^18 - 1), using your formula
with:

=DEC2BIN(INT($B2/512),9)&DEC2BIN(MOD($B2,512),9)

For 262,143, it returns 111111111111111111 (eighteen of them!).
 
DEC2BIN function stops when it tries to display 10 or more characters
(conversion of decimal number 512 or greater). Is there a way to allow it to
do this? I'm looking for 12 or more characters of display.
Click to expand...

Here's a base conversion routine I wrote. It will handle positive numbers,
with and without decimals; and goes up to base 62 by using upper and lower case
alphabetical letters to extend the notation.

So far as base 10 to binary conversion, it converts 999,999,999,999,999
to 11100011010111111010100100110001100111111111111111


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

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

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, "+") Then
BaseConvert = "Cannot use Scientific Notation"
Exit Function
End If

'Convert to Base 10
LDI = InStr(1, num, ".") - 2
If LDI = -2 Then LDI = Len(num) - 1

j = LDI

Temp = Replace(num, ".", "")
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 = CDbl(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

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) = "."
For i = 1 To UBound(Digits)
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
End If

BaseConvert = Temp & Join(Digits, "")

Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description)

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

--ron
 
Back
Top