Binary numbers longer than 10 characters
 

Hi All,

As far as I understand, Excel can only handle 10 character binary numbers.
Is there any code anywhere which allows the handling of larger binary
numbers.

Ta

Andi

Binary numbers longer than 10 characters
 

Take a look he

http://www.mcgimpsey.com/excel/udfs/longdec2bin.html

In article ,
"Andibevan" wrote:

As far as I understand, Excel can only handle 10 character binary numbers.
Is there any code anywhere which allows the handling of larger binary
numbers.

Binary numbers longer than 10 characters
 

Hi Andy,

Not correct.
First, I assume you mean 10-*digit* binary numbers; 10-character binary
numbers would (in most people's opinion) be 80 bits; try figure out what
numbers you can store in an 80-bit binary.
Excel's *precision* is 15 decimal digits. It can store numbers as large as
9.99999999999999E308, but it loses some precision. By the way, 15 digits of
precision means you can loose something like 1/1000,000,000,000th of a
percent in precision. Few application areas require more.
However, if you do require more precision, download the XNUMBERS utility
he

http://digilander.libero.it/foxes/index.htm

If you just need large "numbers" that are really just identifiers with which
you don't have to calculate, like credit card numbers, use text. One way is
to format cells as text before entering the number, another is to precede
the number with a single quote.
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Andibevan" wrote in message
...
Hi All,

As far as I understand, Excel can only handle 10 character binary numbers.
Is there any code anywhere which allows the handling of larger binary
numbers.

Ta

Andi

Binary numbers longer than 10 characters
 

Hi Niek,

Thanks for your guidance, I was a bit brief in my description.

I believed that excel can only handle 10 digit binary numbers as when I
tried "=BIN2DEC(11111111111)" it gave a #num! error

Thanks

Andy

"Niek Otten" wrote in message
...
Hi Andy,

Not correct.
First, I assume you mean 10-*digit* binary numbers; 10-character binary
numbers would (in most people's opinion) be 80 bits; try figure out what
numbers you can store in an 80-bit binary.
Excel's *precision* is 15 decimal digits. It can store numbers as large as
9.99999999999999E308, but it loses some precision. By the way, 15 digits
of
precision means you can loose something like 1/1000,000,000,000th of a
percent in precision. Few application areas require more.
However, if you do require more precision, download the XNUMBERS utility
he

http://digilander.libero.it/foxes/index.htm

If you just need large "numbers" that are really just identifiers with
which
you don't have to calculate, like credit card numbers, use text. One way
is
to format cells as text before entering the number, another is to precede
the number with a single quote.
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Andibevan" wrote in message
...
Hi All,

As far as I understand, Excel can only handle 10 character binary
numbers.
Is there any code anywhere which allows the handling of larger binary
numbers.

Ta

Andi

Binary numbers longer than 10 characters
 

On Sat, 9 Apr 2005 14:39:17 +0100, "Andibevan"
wrote:

Hi All,

As far as I understand, Excel can only handle 10 character binary numbers.
Is there any code anywhere which allows the handling of larger binary
numbers.

Ta

Andi


There are some methods previously posted by Harlan in one of the Excel news
groups. You could do a Google search to find them.

This UDF will handle longer numbers, and also numbers with digits to the right
of the decimal, and bases from 2 to 62.

To use it, enter =BASECONVERT(Num,FromBase,ToBase,DecPlace) with your binary
string as the Num argument; FromBase would be 2 and ToBase whatever base you
want to convert to, e.g. 10. The optional DecPlace argument reflects the
number of digits you would wish to be to the right of the decimal in your
output. It may be left blank.

To enter this UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

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

'by Ron Rosenfeld

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