=TEXT(DEC2BIN(I1&J1&K1),"0000000000")
Here is a UDF (user defined function) that I have posted in the past which will handle values well past the range of numbers anyone might ever care about...M1: =DEC2BIN(SUMPRODUCT(I1:K1,{100,10,1}))
That's limited to values <512, so the second fails.
' The DecimalIn argument is limited to 79228162514264337593543950266
' (approximately 96-bits) - large numerical values must be entered
' as a String value to prevent conversion to scientific notation.
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String
DecToBin = ""
DecimalIn = CDec(DecimalIn)
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
Here is a UDF (user defined function) that I have posted in the past which will handle values well past the range of numbers anyone might ever care about...
Code:' The DecimalIn argument is limited to 79228162514264337593543950266 ' (approximately 96-bits) - large numerical values must be entered ' as a String value to prevent conversion to scientific notation. Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String DecToBin = "" DecimalIn = CDec(DecimalIn) 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
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DecToBin just like it was a built-in Excel function. For your data set as posted in Message #1, this formula would be put in L2 and copied down...
=DecToBin(I2&J2&K2)
Excel Workbook | |||||||
---|---|---|---|---|---|---|---|
H | I | J | K | L | |||
1 | 06/19/12 | 3 | 8 | 3 | 101111111 | ||
2 | 06/18/12 | 5 | 5 | 9 | 1000101111 | ||
3 | 06/17/12 | 1 | 8 | 3 | 10110111 | ||
4 | 06/16/12 | 0 | 1 | 4 | 1110 | ||
5 | 06/15/12 | 9 | 9 | 2 | 1111100000 | ||
6 | 06/14/12 | 0 | 0 | 0 | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L1 | =DecToBin(I1&J1&K1) | |
L2 | =DecToBin(I2&J2&K2) | |
L3 | =DecToBin(I3&J3&K3) | |
L4 | =DecToBin(I4&J4&K4) | |
L5 | =DecToBin(I5&J5&K5) | |
L6 | =DecToBin(I6&J6&K6) |
Ah, the missing leading zeroes. My fault... I forgot to specify the number of bits to be output via the optional second argument. Try using this formula instead...Thank you very much, but I must have did something wrong.
Sheet1
H I J K L 1 06/19/12 3 8 3 101111111 2 06/18/12 5 5 9 1000101111 3 06/17/12 1 8 3 10110111 4 06/16/12 0 1 4 1110 5 06/15/12 9 9 2 1111100000 6 06/14/12 0 0 0
<thead>
</thead><tbody>
</tbody>
All I can say is Thank you very much, fantastic work!!Ah, the missing leading zeroes. My fault... I forgot to specify the number of bits to be output via the optional second argument. Try using this formula instead...
=DecToBin(I2&J2&K2,10)
Where the 10 is the number of binary digits to output.
Excel Workbook | |||||||
---|---|---|---|---|---|---|---|
H | I | J | K | L | |||
1 | 06/19/12 | 3 | 8 | 3 | 1000101111 | ||
2 | 06/18/12 | 5 | 5 | 9 | 0010110111 | ||
3 | 06/17/12 | 1 | 8 | 3 | 0000001110 | ||
4 | 06/16/12 | 0 | 1 | 4 | 1111100000 | ||
5 | 06/15/12 | 9 | 9 | 2 | 0000000000 | ||
6 | 06/14/12 | 0 | 0 | 0 | 0010101101 | ||
7 | 06/13/12 | 1 | 7 | 3 | 1110001001 | ||
8 | 06/12/12 | 9 | 0 | 5 | 0110011101 | ||
9 | 06/11/12 | 4 | 1 | 3 | 1000001111 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L1 | =DecToBin(I2&J2&K2,10) | |
L2 | =DecToBin(I3&J3&K3,10) | |
L3 | =DecToBin(I4&J4&K4,10) | |
L4 | =DecToBin(I5&J5&K5,10) | |
L5 | =DecToBin(I6&J6&K6,10) | |
L6 | =DecToBin(I7&J7&K7,10) | |
L7 | =DecToBin(I8&J8&K8,10) | |
L8 | =DecToBin(I9&J9&K9,10) | |
L9 | =DecToBin(I10&J10&K10,10) |
All I can say is Thank you very much, fantastic work!!
Sheet1Excel 2007
H I J K L 1 06/19/12 3 8 3 1000101111 2 06/18/12 5 5 9 0010110111 3 06/17/12 1 8 3 0000001110 4 06/16/12 0 1 4 1111100000 5 06/15/12 9 9 2 0000000000 6 06/14/12 0 0 0 0010101101 7 06/13/12 1 7 3 1110001001 8 06/12/12 9 0 5 0110011101 9 06/11/12 4 1 3 1000001111
<thead>
</thead><tbody>
</tbody>
Worksheet Formulas
Cell Formula L1 =DecToBin(I2&J2&K2,10) L2 =DecToBin(I3&J3&K3,10) L3 =DecToBin(I4&J4&K4,10) L4 =DecToBin(I5&J5&K5,10) L5 =DecToBin(I6&J6&K6,10) L6 =DecToBin(I7&J7&K7,10) L7 =DecToBin(I8&J8&K8,10) L8 =DecToBin(I9&J9&K9,10) L9 =DecToBin(I10&J10&K10,10)
<thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>