# Convert Values to Binary Code

#### CARBOB

##### Well-known Member
Have no idea what code the values in column L are, but I know they repesent the values in I:K.
Excel Workbook
HIJKL
106/19/123830101111111
206/18/125591000101111
306/17/121830010110111
406/16/120140000001110
Sheet1
Excel 2007

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Re: Convert Values to Binary Code????

Looks like a DEC2BIN conversion of the concatenated values (383, 559, 183, 014) but with some special formatting applied to force the result to 10 digits. Maybe:

Code:
``=TEXT(DEC2BIN(I1&J1&K1),"0000000000")``

Re: Convert Values to Binary Code????

M1: =DEC2BIN(SUMPRODUCT(I1:K1,{100,10,1}))

That's limited to values <512, so the second fails.

Re: Convert Values to Binary Code????

You are correct, the formula works for values less than 512. The database is a txt file using this format
06/19/2012 3 8 3 0101111111
06/18/2012 5 5 9 1000101111
06/17/2012 1 8 3 0010110111
06/16/2012 0 1 4 0000001110
06/15/2012 9 9 2 1111100000

Re: Convert Values to Binary Code????

Hi Carbob,

The second one is 512+32+8+4+2+1 or 2^9+2^5+2^3+2^2+2^1+2^0

Last edited:
Re: Convert Values to Binary Code????

M1: =DEC2BIN(SUMPRODUCT(I1:K1,{100,10,1}))

That's limited to values <512, so the second fails.
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)

Re: Convert Values to Binary Code????

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)

Thank you very much, but I must have did something wrong.
Excel Workbook
HIJKL
106/19/12383101111111
206/18/125591000101111
306/17/1218310110111
406/16/120141110
506/15/129921111100000
606/14/12000
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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)

Re: Convert Values to Binary Code????

Thank you very much, but I must have did something wrong.
Sheet1
HIJKL
106/19/12383101111111
206/18/125591000101111
306/17/1218310110111
406/16/120141110
506/15/129921111100000
606/14/12000

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

Re: Convert Values to Binary Code????

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.
All I can say is Thank you very much, fantastic work!!
Excel Workbook
HIJKL
106/19/123831000101111
206/18/125590010110111
306/17/121830000001110
406/16/120141111100000
506/15/129920000000000
606/14/120000010101101
706/13/121731110001001
806/12/129050110011101
906/11/124131000001111
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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)

Re: Convert Values to Binary Code????

All I can say is Thank you very much, fantastic work!!
Sheet1
HIJKL
106/19/123831000101111
206/18/125590010110111
306/17/121830000001110
406/16/120141111100000
506/15/129920000000000
606/14/120000010101101
706/13/121731110001001
806/12/129050110011101
906/11/124131000001111

</tbody>
Excel 2007

Worksheet Formulas
CellFormula
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)

</tbody>

<tbody>
</tbody>

Just to point out for clarification... all of your (first) table values are off by one row from the values the function is converting (see the red highlighted text in the first table for but one example). The problem came about because you did not adjust the cell references being passed into the function for the row that the data is on. See the purple highlighted cell in the second table... since the first formula is located in Row 1, the assumption is your data is also in Row 1, so the argument to the function I2&J2&K2 should actually be I1&J1&K1 so that the right cell values are being passed to the function for the row the function is referencing.

Replies
12
Views
588
Replies
4
Views
109
Replies
3
Views
96
Replies
7
Views
78
Replies
0
Views
345

1,203,683
Messages
6,056,730
Members
444,887
Latest member
cvcc_wt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back