Using DEC2BIN() with large numbers

Question

I'm trying to convert 4503599627370495 into binary in Excel. DEC2BIN() returns #NUM! error because DEC2BIN cannot handle such a large number.

Any thoughts on how I might be able to make it work?

Answer 1

This is super simple, Base(...) function can help you.

BASE(CELL, 2)

The second param 2 is for binary, you can convert to other relevant bases as Hex, Oct

Answer 2

Thanx JustinDavies - that was just what I needed, but it went into an endless loop if passed a -ve number. My modification:

Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String
  DecToBin = ""
  DecimalIn = CDec(DecimalIn)
  If DecimalIn < 0 Then
    DecToBin = "Error - Number negative"
    Exit Function
  End If
  Do While DecimalIn <> 0
    DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin
    DecimalIn = Int(DecimalIn / 2)
  Loop
  If Not IsMissing(NumberOfBits) Then
    If Len(DecToBin) > NumberOfBits Then
      DecToBin = "Error - Number too large for bit size"
    Else
      DecToBin = Right$(String$(NumberOfBits, "0") & _
      DecToBin, NumberOfBits)
    End If
  End If
End Function