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