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

Take a look here:

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

In article <#[email protected]>,
"Andibevan" <[email protected]> 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.

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
here:

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> here:
>
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
>
>

On Sat, 9 Apr 2005 14:39:17 +0100, "Andibevan"
<[email protected]> 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