View Single Post
  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] ilya.kosykh@gmail.com is offline
external usenet poster
 
Posts: 1
Default BIN2DEC conversion for large binary numbers

On Wednesday, December 16, 2009 6:28:27 AM UTC+4, Rick Rothstein wrote:
Below is a UDF that will handle up to a 96-bit binary number (decimal value
79228162514264337593543950335) which I'm guessing is way more than you will
ever need.<g The code is efficient (looping only as many times as necessary
to process the passed in binary value), so don't worry about it being able
to handle such a large binary value. The function returns a real numeric
value up to 9999999999 after which it returns text representations of the
calculated number.

Function BinToDec(BinaryString As String) As Variant
Dim X As Integer
Const TwoToThe48 As Variant = 281474976710656#
For X = 0 To Len(BinaryString) - 1
If X 48 Then
BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
Len(BinaryString) - X, 1)) * _
TwoToThe48 * CDec(2 ^ (X - 48))
Else
BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
Len(BinaryString) - X, 1)) * CDec(2 ^ X)
End If
Next
If Len(BinToDec) 10 Then BinToDec = CStr(BinToDec)
End Function

--
Rick (MVP - Excel)



Hi Rick,

Great thanks for this. That's wonderfull - i am not a programmer and am trying to solve a logical puzzle and have ended up sitting here and looking for the way to convert 65-bit numbers from decimal to binary and back. Your solution is the best of everything that i have managed to find in internet (and have spent 3 days already for this).

Could you please be so kind as to make a UDF for converting 96-bit decimal number to binary (or 65-bit would be enough for me). As this is something that i still struggle to figure out?

Anyone else? Please help?

Ilya