Binary Numbers longer than 10 characters

[Andibevan]

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

 

[Bernie Deitrick]

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

 

[Harlan Grove]

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"))))