Remember Me? August 4th 17, 02:26 PM posted to microsoft.public.excel.worksheet.functions
 [email protected] external usenet poster First recorded activity by ExcelBanter: Aug 2017 Posts: 1 BIN2DEC conversion for large binary numbers

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?

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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post tam Excel Worksheet Functions 9 November 30th 13 07:30 PM Sanford Lefkowitz Excel Discussion (Misc queries) 9 May 12th 10 04:06 PM xcgames Excel Worksheet Functions 2 March 27th 06 12:19 AM Navy Student Excel Worksheet Functions 6 September 1st 05 03:11 PM Himu Excel Worksheet Functions 4 July 27th 05 02:53 AM

All times are GMT +1. The time now is 01:02 AM. Copyright ©2004-2019 ExcelBanter.