Hi All,
As far as I understand, Excel can only handle 10 character binary numbers.
Is there any code anywhere which allows the handling of larger binary
numbers.
Ta
Andi
There are some methods previously posted by Harlan in one of the Excel news
groups. You could do a Google search to find them.
This UDF will handle longer numbers, and also numbers with digits to the right
of the decimal, and bases from 2 to 62.
To use it, enter =BASECONVERT(Num,FromBase,ToBase,DecPlace) with your binary
string as the Num argument; FromBase would be 2 and ToBase whatever base you
want to convert to, e.g. 10. The optional DecPlace argument reflects the
number of digits you would wish to be to the right of the decimal in your
output. It may be left blank.
To enter this UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.
=======================================
Function BaseConvert(Num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String
'by Ron Rosenfeld
Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String
DecSep = Application.International(xlDecimalSeparator)
On Error GoTo HANDLER
If FromBase > 62 Or ToBase > 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If
If InStr(1, Num, "E") And FromBase = 10 Then
Num = CDec(Num)
End If
'Convert to Base 10
LDI = InStr(1, Num, DecSep) - 2
If LDI = -2 Then LDI = Len(Num) - 1
j = LDI
Temp = Replace(Num, DecSep, "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 >= FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i
If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0
ReDim Digits(LDI)
For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)
If r <> 0 Then
Digits(0) = DecSep
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If
BaseConvert = Temp & Join(Digits, "")
Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & Num)
End Function
================================
--ron
