On Tuesday, December 15, 2009 at 10:19:13 AM UTC-6, ahmedmidany wrote:
Hello All,

I need your help, i want to convert large binary numbers using excel
but whenever i use the BIN2DEC function the result is negative which
is not correct.

Ex. BIN2DEC(1100110110) the result is -202 but if i use the calculator
the result is 822 which is the correct value

Any ideas? what shall i do to have the correct value?

Thanks in advance
A.M.

While I didn't write this for negatives or decimals, it should be relatively easy to modify. This VBA will convert any super large (or not so large if you want...but that wasn't the point) decimal up to the converted binary result containing up to 32767 digits (maximum string length in VBA). Enter decimal in Cell "A1" as a string, result will be in "B1" as a string.

Dim NBN As String
Dim Bin As String

5 Big = Range("A1")
AA = Len(Big)

For XX = 1 To AA

L1 = Mid(Big, XX, 1) + CRY

CRY = 0

If L1 = 0 Then

FN = "0"

GoTo 10

End If

If Int(L1 / 2) = L1 / 2 Then

FN = L1 / 2

GoTo 10

End If

If Int(L1 / 2) < L1 / 2 Then

FN = Int(L1 / 2)

CRY = 10

GoTo 10

End If

10 NBN = NBN & FN
Next XX

If Left(NBN, 1) = "0" Then

NBN = Right(NBN, (Len(NBN) - 1))

End If

If CRY = 10 Then Bin = "1" & Bin Else Bin = "0" & Bin

Range("A1") = NBN

Range("A2") = Bin

If Len(NBN) 0 Then

NBN = ""

CRY = 0

GoTo 5

End If