Binary Numbers longer than 10 characters
 

Hi All,

As far as I understand the standard Bin2Dec and Dec2Bin functions can only
handle 10 character binary numbers.

Does anyone have any pointers on how to handle binary numbers greater than
this?

If I have completely mis-understood how excel handles binary numbers feel
free to let me know.

Andi

Andi,

Myrna Larson to the rescue.... below is her classic post on the subject.

HTH,
Bernie
MS Excel MVP

You could use a custom VBA function to convert the base. The one below will
handle decimal integers with up to 15 digits, and can convert to any base
from
2 through 36.

Be forewarned, this is considerably slower than using the built-in functions
in the ATP, but it does handle a wider range for binary. (ATP is limited to
0-511, 10 bits).

The syntax for binary is =ConvertToBase(A1,2)
Octal, =ConvertToBase(A1,8)
Hex, =ConvertToBase(A1,16)

etc.


Function ConvertToBase(ByVal lValue As Variant, iBase As Integer) _
As String

Const sDigits = "123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Const MaxLen = 56
Dim IsNeg As Boolean
Dim sNumber As String
Dim p As Integer
Dim iDigit As Integer
Dim PrevValue As Variant

'Trap base value errors
If (iBase 36) Or (iBase < 2) Then Exit Function

IsNeg = False
If lValue < 0 Then
IsNeg = True
lValue = -lValue
End If

sNumber = String$(MaxLen, "0")
p = MaxLen + 1

Do While lValue 0
PrevValue = lValue
lValue = Int(lValue / iBase)
iDigit = PrevValue - lValue * iBase
p = p - 1
If iDigit Then Mid$(sNumber, p, 1) = Mid$(sDigits, iDigit, 1)
Loop

If p MaxLen Then p = p - 1

If IsNeg Then
p = p - 1
Mid$(sNumber, p, 1) = "-"
End If

ConvertToBase = Mid$(sNumber, p)
End Function 'ConvertToBase



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

As far as I understand the standard Bin2Dec and Dec2Bin functions can only
handle 10 character binary numbers.

Does anyone have any pointers on how to handle binary numbers greater than
this?

If I have completely mis-understood how excel handles binary numbers feel
free to let me know.

Andi

Andibevan wrote...
As far as I understand the standard Bin2Dec and Dec2Bin functions can
only
handle 10 character binary numbers.

Does anyone have any pointers on how to handle binary numbers greater
than
this?

If I have completely mis-understood how excel handles binary numbers
feel
free to let me know.

You've been given a udf, which is flexible enough to handle many
radices. A quicker alternative would be downloading and installing
Laurent Longre's MOREFUNC.XLL add-in, available from

http://longre.free.fr/english

which includes a function named CHBASE that does the same thing.

If all you need to work with are binary numbers, you could forgo both
udfs and add-ins and use divide-and-conquer.

A1: (decimal number)
7654321

A2: (binary string with leading zeros)
=DEC2BIN(INT(A1/2^24),8)&DEC2BIN(INT(MOD(A1,2^24)/2^16),8)
&DEC2BIN(INT(MOD(A1,2^16)/2^8),8)&DEC2BIN(MOD(A1,2^8),8)

A3: (binary string without leading zeros)
=MID(DEC2BIN(INT(A1/2^24),8)&DEC2BIN(INT(MOD(A1,2^24)/2^16),8)
&DEC2BIN(INT(MOD(A1,2^16)/2^8),8)&DEC2BIN(MOD(A1,2^8),8),
INT(33-LOG(A1,2)),32)

A4: (decimal number derived from A2 - would be the same for A3)
=SUMPRODUCT(--MID(RIGHT(REPT("0",32)&A2,32),ROW(INDIRECT("1:32") ),1),
2^(32-ROW(INDIRECT("1:32"))))