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 ~~Toolpack~~Toolpak.” 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.

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?

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!

:-)

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

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

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

no, its not working.

send me your email address, and I’ll email you the code.

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.

:)

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

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.

in excel you can use the BIN function mate!

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

Oops had a bug. See corrected source below

Dim result As String

Dim quotient As Long

Do

result = (number Mod 2) & result

number = Int(number / 2)

Loop While number > 0

Base2 = result

End Function

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?

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

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

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)))

Excellent code. Works great! Thanks.

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

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”…

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

try this….

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

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.

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

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:

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″))

There appears to be a tradition of multiple posts here, so here goes, for 24 bits:

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″))

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