Decimal To Binary

cal comments:

I am a printer that is trying to print a medical form that is read by the an OCR. Each page has a unique contract number like 567301 then there is a column that has a series of 24 “bubbles” (like on an SAT test). The bubbles are colored in based on converting the contract number to a binary number. Any ideas on how I could use Excel to print this?

“Oh boy”, I thought, “I finally get to use the DEC2BIN function in the Analysis ToolpackToolpak.” I got the #NUM! error and subsequently learned that the maximum argument is 511. Five hundred eleven? If I was a teenager IM’ing my buddies, I might text “WTF?”. Is 511 the most anyone would want to convert to binary? I must be missing something.

Whenever I have a problem that needs an Excel solution, I first check The Spreadsheet Page because John’s done almost everything. Then I go to some Excel blog so I stop being embarrassed asking questions that I or someone else has already answered.

No dice at The SS Page, but I found this Analysis Toolpak post by the late Frank Kabal. It’s good to 15 decimal places (an Excel limit), but it looks like cal needs 24. Also, there’s no way to pad the insignificant digits. Hooray, I get to write my own. I’ll use Bitwise And.

Function Dec2Bin2(lNumber As Long, _
    Optional lPlaces As Long = 4) As String
   
    Dim i As Long
    Dim sBin As String
   
    i = 1
       
    Do
        sBin = IIf((Abs(lNumber) And i) = 0, “0”, “1”) & sBin
        i = i * 2
    Loop Until i > Abs(lNumber)
   
    For i = Len(sBin) + 1 To lPlaces
        sBin = “0” & sBin
    Next i
       
    Do Until Len(sBin) Mod 4 = 0
        sBin = “0” & sBin
    Loop
   
    If lNumber < 0 Then
        Dec2Bin2 = TwosComp(sBin)
    Else
        Dec2Bin2 = sBin
    End If
   
End Function
 
Function TwosComp(sBin As String) As String
   
    Dim i As Long
    Dim lNum As Long
    Dim sTemp As String
   
    For i = 1 To Len(sBin)
        sTemp = sTemp & IIf(Mid(sBin, i, 1) = 0, “1”, “0”)
    Next i
   
    For i = Len(sTemp) To 1 Step -1
        lNum = lNum + ((2 ^ (i – 1)) * Val(Mid$(sTemp, Len(sTemp) – i + 1, 1)))
    Next i
   
    TwosComp = Dec2Bin2(lNum + 1, Len(sBin))
   
End Function

The first decision I made was that the number of places had to be a multiple of 4. There’s nothing wrong with a six bit binary representation, but things got a little complicated with the two’s notation part. Originally I set the optional argument equal to 4 because it just doesn’t seem right to represent 2 as 10. You’ve got to show at least four significant digits or there’s something wrong with the universe.

Then I ran into a little problem with certain numbers. Eight, for instance. Eight in binary is 1000. The two’s complement representation of -8 is 1000 if you only show four digits, which is wrong. In that situation, can I just add one digit (11000)? Does the user of the output have to know how many bits in order to interpret the output correctly? It seems so to me, but I’m not really an expert on binary. Excel’s DEC2BIN shows 10 significant digits when the argument is negative, but that seems a bit arbitrary to me. At least I know what to expect, though. So I made the output a multiple of four which allows for some predictability, but there are still problems.

=Dec2Bin2(15) = 1111
=Dec2Bin2(-15) = 0001

In this case, I need to add more bits to make the negative meaningful, but I don't have to do it for, say, 2 and -2 (0010 and 1110) or do I? So the question is: How many bits do I show and how do I determine that? Suggestions?

Posted in Uncategorized

26 thoughts on “Decimal To Binary

  1. I came to your page googling for something else, but liked your page, and read through it. I’ll bookmark you.

    In your example of 2 and -2 (0010 and 1110), there IS room for misinterpretation, since 14 is also (1110) and -14 is (0010).

    To make it clear you are referring to a negative number, you need another “rule”, that is, with “n” binary bits, your maximum positive number is 2^(n-1). That means your 4 bit binaries have a maximum value of 7 (0111). You currently show 8 as (1000) which is incorrect under this rule. You should show 8 as (01000), or (00001000) if you are using multiples of 4 bits.

    Remember the good old days of Z80 8 bit processors? Any binary starting with a “1? is negative!

    :-)

  2. Sorry Dick, may I add another comment to my previous post…

    I would recommend (in your hopefully updated VBA Dec2Bin2 to fix -ve numbers) that you implement…

    OPTIONAL forcen

    Where forcen creates a binary of length n, if the user selects this. Otherwise, it would use your automatic multiple of 4 rule, including my previous comments.

    (I guess you could implement this by using your automatic output within a =RIGHT(REPT(if(lnumber>=0,”0?,”1?),n)&DEC2BIN2, n), but it would be more elegant to have this WITHIN the vba).

    David

  3. Ok. last comments. Here is the function. (I guess I needed something to occupy me for a while)

    Notice that I have replaced your TwosCompliment function with a binary NOT.

    For negative numbers, it is quicker to take the abs decimal, subtract one, do the binary, then NOT the result.

    (Your algorithm was a bit long-winded, with the need to call the binary producer twice).

    Please feel free to edit/adapt this before posting. After all, its based on your original code:

    Function Dec2Bin2(lNumber As Long, _
    Optional forcemin As Variant, Optional forcemax As Variant) As String
    ‘ create a binary string from a decimal integer
    ‘ with a minimum number of binary places, forcemin
    ‘ and a maximum number of binary places, forcemax
    ‘ note we need to use Variant for Optional variables. This is good practice
    ‘ since, e.g., an integer cannot have a “missing” flag

    ‘ WARNING
    ‘ with the forcemax option set, there is a risk of “overflow” and
    ‘ the output may have digits missing

    Dim i As Long
    Dim lNumberAbs As Long
    Dim sBin As String
    Dim lPlaces As Integer

    ‘ set the number of binary places as forcemin, if valid, else 4
    If IsNumeric(forcemin) And IsEmpty(forcemin) = False Then
    lPlaces = forcemin
    Else
    lPlaces = 4
    End If

    ‘ create a binary negative from the NOT of the positive minus 1
    lNumberAbs = Abs(lNumber) + IIf(lNumber lNumberAbs

    ‘all positive binary numbers start with a “0?!
    If lNumberAbsComp > 0 Then
    sBin = “0? & sBin
    End If

    For i = Len(sBin) + 1 To lPlaces
    sBin = “0? & sBin
    Next i

    If IsNumeric(forcemin) = False Then
    Do Until Len(sBin) Mod 4 = 0
    sBin = “0? & sBin
    Loop
    End If

    If IsNumeric(forcemax) And IsEmpty(forcemax) = False Then
    sBin = Right(sBin, forcemax)
    End If

    If lNumber

  4. Something isn’t pasting properly, or showing up on your HTTP, so I’ll try again:

    ‘ create a binary negative from the NOT of the positive minus 1
    lNumberAbs = Abs(lNumber) + IIf(lNumber lNumberAbs

  5. In order to keep this generic one has few solutions

    1. Argument lPlaces is NOT OPTIONAL. This way it is ok to return 2nd complement for a negative number, but it assumes integer format.

    2. Simply return “-” in front of the binary string for negative numbers. There is nothing wrong with this, as long as it stays in an Excel cell (the application has nothing to do with micro- processors/controllers)

    3. Add format argument to the function and allow for: integer, unsigned integer, BCD, float.

    Function Dec2Bin2(lNumber As Long, _
    lPlaces As Long, _
    sFormat as String) _
    As String

    It would be nice to have matching Bin2Dec too.

    :)

  6. Ok-
    Please summarize this dicussion for me. I am not a VB programmer. If you would, with David’s and Damir’s comments what does the code look like?

    My goal is to convert 32 bit two’s complement to binary. If necessary, I can drop back to 16 bit two’s complement.

    I want to check every bit for status.

    Thank you

  7. hi guys,

    i was dealing with the extended euclidean algorithm where I needed a decimal to binary converter for excel, so here is the code:

    Sub Dec2Bin()
    Dim n, dsayi, arasayi, bsayi
    dsayi = Range(“A1?).Value
    n = Int(Log(dsayi) / Log(2))
    arasayi = dsayi
    bsayi = 0
    For i = 0 To n
    bsayi = bsayi + (arasayi Mod 2) * 10 ^ i
    arasayi = Int(arasayi / 2)
    Next
    Range(“A2?).Value = bsayi
    Range(“A2?).Select
    Selection.NumberFormat = “0?
    End Sub

    Copy and paste it into an excel macro.
    You have to put the decimal number into “A1? and run the macro. A2 should now include the binary number.

    A few explanations
    dsayi => decimal number
    bsayi => binary number
    arasayi => temporary stack
    n => number of turns to get the binary number

    I’m not an VB programmer, so don’t blame me for anything.

  8. The following function can be used to convert from base 10 (decimal) to base 2 (binary). It is implemented to return a number which results in a loss of leading zeros but the result cell can easily be formatted to display as many leading zeros as required by setting a custom format e.g. a custom format of 0000 would result in the addition of leading zeros to the result cell up to 4-bits wide.

    Function Base2(number As Long) As Long
    Dim result As String
    Dim quotient As Long
    Do
    result = result & (number Mod 2)
    number = CLng(number / 2)
    Loop While number > 0
    Base2 = StrReverse(result)
    End Function

  9. Oops had a bug. See corrected source below

    Function Base2(number As Long) As Long
        Dim result As String
        Dim quotient As Long
        Do
            result = (number Mod 2) &amp; result
            number = Int(number / 2)
        Loop While number &gt; 0
        Base2 = result
    End Function
  10. i am try to use excel to convert a 32 bit bin2dec, obviously excel will only do upto 10 bit. this looks like the most informative string i have found on this topic. anyone have any ideas?

  11. here is another version, works on positive and _negative_ numbers:

    Function Long2Bin(lNumber As Long, Optional lPlaces As Long = 8) As String
    ‘ converts a decimal number to a binary string

    Static vDigits As Variant ‘ digits used in the result string

    Dim lVal As Long ‘ temporary value
    Dim sTemp As String ‘ to build up the result string

    ‘ init digits on first call of function
    If IsEmpty(vDigits) Then vDigits = Array(“0?, “1?)

    ‘ corrections for negative numbers
    lVal = Abs(lNumber) ‘ always work on positive numbers
    If lNumber 0) Or (lPlaces > 0)
    sTemp = vDigits(lVal Mod 2) & sTemp ‘ add char in front of string
    lPlaces = lPlaces – 1
    lVal = lVal 2 ‘ integer division by 2
    Loop

    ‘ one’s complement for negative numbers
    If lNumber

  12. another try…


    Function Long2Bin(lNumber As Long, Optional lPlaces As Long = 8) As String
    ' converts a decimal number to a binary string

    Static vDigits As Variant ' digits used in the result string

    Dim lVal As Long ' temporary value
    Dim sTemp As String ' to build up the result string

    ' init digits on first call of function
    If IsEmpty(vDigits) Then vDigits = Array("0", "1")

    ' corrections for negative numbers
    lVal = Abs(lNumber) ' always work on positive numbers
    If lNumber 0) Or (lPlaces > 0)
    sTemp = vDigits(lVal Mod 2) & sTemp ' add char in front of string
    lPlaces = lPlaces - 1
    lVal = lVal 2 ' integer division by 2
    Loop

    ' one's complement for negative numbers
    If lNumber

  13. Use this formula to convert the Binary(10100111000000) number in cell G19 to decimal number.

    =SUMPRODUCT(2^(ROW(INDIRECT(“1:”&LEN(G7)))-1),VALUE(MID(G7,LEN(G7)-ROW(INDIRECT(“1:”&LEN(G7)))+1,1)))

  14. While this function (which works fine as a UDF) only handles positive values, it will return the Binary string for values up to 9999999999999999999999999999. I’m thinking that should be a large enough range to handle whatever you may want to throw at it. If the value you want to process is larger than Excel can properly handle as whole number, simply pass the value in as text. So, for the maximum value the function can handle, you would call it like this from VB…

    MsgBox DecToBin(“9999999999999999999999999999?)

    or like this from the worksheet…

    =DecToBin(“9999999999999999999999999999?)

    or, if A1 contains ‘9999999999999999999999999999 (note the leading apostrophe) or 9999999999999999999999999999 if formatted as Text, then like this…

    =DecToBin(A1)

    Okay, here is the function (note that the Analysis ToolPak has a similarly named function which uses a “2? to link the words “Dec” and “Bin” instead of the word “To” that my function does)…

    Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant = 0) As Variant
    If DecimalIn < 0 Then
    ‘ Negative values cannot be converted
    DecToBin = CVErr(xlErrValue)
    Exit Function
    Else
    DecimalIn = Int(CDec(DecimalIn))
    Do While DecimalIn 0
    DecToBin = Trim$(Str$(DecimalIn – 2 * Int(DecimalIn / 2))) & DecToBin
    DecimalIn = Int(DecimalIn / 2)
    Loop
    If NumberOfBits > 0 Then
    If Len(DecToBin) > NumberOfBits Then
    ‘ Calculated value exceeds specified bit size
    DecToBin = CVErr(xlErrValue)
    Else
    DecToBin = Right$(String$(NumberOfBits, “0?) & DecToBin, NumberOfBits)
    End If
    End If
    End If
    End Function

  15. Sorry, I forgot the “code tags”… I just noticed some of my lines have been corrupted. Here is my code again, but this time using the “code tags”…

    Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant = 0) As Variant
      If DecimalIn < 0 Then
        ‘  Negative values cannot be converted
       DecToBin = CVErr(xlErrValue)
        Exit Function
      Else
        DecimalIn = Int(CDec(DecimalIn))
        Do While DecimalIn <> 0
          DecToBin = Trim$(Str$(DecimalIn – 2 * Int(DecimalIn / 2))) & DecToBin
          DecimalIn = Int(DecimalIn / 2)
        Loop
        If NumberOfBits > 0 Then
          If Len(DecToBin) > NumberOfBits Then
            ‘  Calculated value exceeds specified bit size
           DecToBin = CVErr(xlErrValue)
          Else
            DecToBin = Right$(String$(NumberOfBits, “0”) & DecToBin, NumberOfBits)
          End If
        End If
      End If
    End Function
  16. try this….

    =FIND(“0”,CONCATENATE(DEC2BIN(LEFT(c3,FIND(“.”,c3)-1)),DEC2BIN(MID(c3,FIND(“.”,c3)+1,FIND(“.”,c3,FIND(“.”,c3)+1)-FIND(“.”,c3)-1)),DEC2BIN(MID(c3,FIND(“.”,c3,FIND(“.”,c3)+1)+1,FIND(“.”,c3,FIND(“.”,c3,FIND(“.”,c3)+1)+1)-FIND(“.”,c3,FIND(“.”,c3)+1)-1)),DEC2BIN(RIGHT(c3,LEN(c3)-FIND(“.”,c3,FIND(“.”,c3,FIND(“.”,c3)+1)+1))),”0″))-1
  17. sorry for my missed post to the wrong place, my below formular is for convert subnet mask to number of mask bit such as 255.255.255.0 to /24

    =FIND(“0”,CONCATENATE(DEC2BIN(LEFT(c3,FIND(“.”,c3)-1)),DEC2BIN(MID(c3,FIND(“.”,c3)+1,FIND(“.”,c3,FIND(“.”,c3)+1)-FIND(“.”,c3)-1)),DEC2BIN(MID(c3,FIND(“.”,c3,FIND(“.”,c3)+1)+1,FIND(“.”,c3,FIND(“.”,c3,FIND(“.”,c3)+1)+1)-FIND(“.”,c3,FIND(“.”,c3)+1)-1)),DEC2BIN(RIGHT(c3,LEN(c3)-FIND(“.”,c3,FIND(“.”,c3,FIND(“.”,c3)+1)+1))),”0″))-1
  18. This won’t handle negative numbers. It will handle anything up to VBA’s maximum string length, but it takes its input number and returns the transformation as strings. The “From” and “To” bases are decimal numbers. Either base can be anything from 2 to 36. I’m cutting & pasting from something else, so I hope I get all that right! Error checking is done elsewhere in my code.

    Function ChangeBase(ByVal sFrom As String, _
                              sTo As String, _
                        ByVal nFrom As Long, _
                        ByVal nTo As Long) As Boolean

    If Len(sFrom) = 0 Then
        sTo = “0”
    Else
        Dim bNonZero As Boolean
        Dim i As Long
        For i = 1 To Len(sFrom)
            If Mid$(sFrom, i, 1) <> “0” Then
                bNonZero = True
                Exit For
            End If ‘Mid$(sFrom, i, 1) <> “0”
       Next i
        If bNonZero Then
            sTo = “”
            Do ‘Until Len(sFrom) = 0
               If Left$(sFrom, 1) = “0” Then
                    sFrom = Right$(sFrom, Len(sFrom) – 1)
                Else
                    Dim nCarry As Long
                    nCarry = 0
                    For i = 1 To Len(sFrom)
                        Dim nNum As Long
                        If CharToNum(Mid$(sFrom, i, 1), nNum) Then Err.Raise knCall, , ksCall
                        Dim n As Long
                        n = nFrom * nCarry + nNum
                        nCarry = n Mod nTo
                        Dim sNum As String
                        If NumToChar(n nTo, sNum) Then Err.Raise knCall, , ksCall
                        Mid$(sFrom, i, 1) = sNum
                    Next i
                    If NumToChar(nCarry, sNum) Then Err.Raise knCall, , ksCall
                    sTo = sNum & sTo
                End If ‘Left$(sFrom, 1) = “0”
           Loop Until Len(sFrom) = 0
        Else
            sTo = sFrom ‘All zeros
       End If ‘bNonZero
    End If ‘Len(sFrom) = 0

    End Function

    Function CharToNum(s As String, n As Long) As Boolean

    If IsNumeric(s) Then
        n = CLng(s)
    Else
        n = Asc(s) – 55
    End If

    End Function

    Function NumToChar(n As Long, s As String) As Boolean

    If n < 10 Then
        s = CStr(n)
    Else
        s = Chr$(55 + n)
    End If

    End Function

  19. Was looking for a formula (not VBA), found this page, kept on looking, gave up looking, came up with the following instead.
    Gives you 12-bit binary, no negative numbers, craps out on larger numbers.
    Only have German version of xl on hand … VERKETTEN > CONCAT, WENN > IF, REST > MOD
    Example for cell E2:

    =VERKETTEN(
    WENN(REST(E2;4096)>=2048;”1″;”0″);
    WENN(REST(E2;2048)>=1024;”1″;”0″);
    WENN(REST(E2;1024)>=512;”1″;”0″);
    WENN(REST(E2;512)>=256;”1″;”0″);
    WENN(REST(E2;256)>=128;”1″;”0″);
    WENN(REST(E2;128)>=64;”1″;”0″);
    WENN(REST(E2;64)>=32;”1″;”0″);
    WENN(REST(E2;32)>=16;”1″;”0″);
    WENN(REST(E2;16)>=8;”1″;”0″);
    WENN(REST(E2;8)>=4;”1″;”0″);
    WENN(REST(E2;4)>=2;”1″;”0″);
    WENN(REST(E2;2)>=1;”1″;”0″))
  20. There appears to be a tradition of multiple posts here, so here goes, for 24 bits:

    =CONCATENATE(
    IF(MOD(E2,2^24)>=2^23,”1″,”0″),
    IF(MOD(E2,2^23)>=2^22,”1″,”0″),
    IF(MOD(E2,2^22)>=2^21,”1″,”0″),
    IF(MOD(E2,2^21)>=2^20,”1″,”0″),
    IF(MOD(E2,2^20)>=2^19,”1″,”0″),
    IF(MOD(E2,2^19)>=2^18,”1″,”0″),
    IF(MOD(E2,2^18)>=2^17,”1″,”0″),
    IF(MOD(E2,2^17)>=2^16,”1″,”0″),
    IF(MOD(E2,2^16)>=2^15,”1″,”0″),
    IF(MOD(E2,2^15)>=2^14,”1″,”0″),
    IF(MOD(E2,2^14)>=2^13,”1″,”0″),
    IF(MOD(E2,2^13)>=2^12,”1″,”0″),
    IF(MOD(E2,2^12)>=2^11,”1″,”0″),
    IF(MOD(E2,2^11)>=2^10,”1″,”0″),
    IF(MOD(E2,2^10)>=2^9,”1″,”0″),
    IF(MOD(E2,2^9)>=2^8,”1″,”0″),
    IF(MOD(E2,2^8)>=2^7,”1″,”0″),
    IF(MOD(E2,2^7)>=2^6,”1″,”0″),
    IF(MOD(E2,2^6)>=2^5,”1″,”0″),
    IF(MOD(E2,2^5)>=2^4,”1″,”0″),
    IF(MOD(E2,2^4)>=2^3,”1″,”0″),
    IF(MOD(E2,2^3)>=2^2,”1″,”0″),
    IF(MOD(E2,2^2)>=2^1,”1″,”0″),
    IF(MOD(E2,2^1)>=2^0,”1″,”0″))
  21. I have created a simple function which can extract any BIT from integer. With that you can convert any INT size, just adding the bits.

    Function GetBit(num As Integer, bit As Integer) As Integer
    GetBit = (num Mod (2 ^ (bit + 1)) – num Mod (2 ^ bit)) / (2 ^ bit)
    End Function


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.