I would like to take a 16-bit integer value, convert it to binary and then
break the individual 1s and 0s into seperate columns.

For example, a value of 5 in column A to break out into columns C,D,E,F as
0,1,0,1 respectivly.

I need to be able to use the individual 1s and 0s later in the sheet.

On Wed, 27 Aug 2008 12:16:05 -0700, DCLittlejohn
<(E-Mail Removed)> wrote:

>I would like to take a 16-bit integer value, convert it to binary and then
>break the individual 1s and 0s into seperate columns.
>
>For example, a value of 5 in column A to break out into columns C,D,E,F as
>0,1,0,1 respectivly.
>
>I need to be able to use the individual 1s and 0s later in the sheet.

A1: 5
C1: =MID(DEC2BIN($A$1,4),COLUMNS($A:A),1)

Fill right to F1



--ron

>I would like to take a 16-bit integer value, convert it to binary and then
> break the individual 1s and 0s into seperate columns.
>
> For example, a value of 5 in column A to break out into columns C,D,E,F as
> 0,1,0,1 respectivly.
>
> I need to be able to use the individual 1s and 0s later in the sheet.

Your example shows 0101 as the value of 5... technically, that is correct,
but it is *not* a 16-bit binary value (it's a 4-bit binary value)... the
16-bit binary values would be 0000000000000101. So you will need to clarify
what exactly you want returned. In anticipation that your 16-bit statement
was correct (and 0101 was due to hurried typing), you can use the VBA macro
(which relies on the accompanying function) after my signature to populate
the cells you indicated you wanted filled in. Simply change the values being
assigned in the Const statement (if they end up being different from what
posted) as necessary (the WorksheetName constant definitely needs to be
looked at to see if you need to).

If you are not familiar with macros and functions, you would implement my
code by going into the VBA editor (press Alt+F11 from any worksheet), click
Insert/Module from the editor's menu bar, and copy paste all the code after
my signature into the code window that opened up. Now, go back to the
worksheet you want to have this functionality on (Sheet1 as set in my code),
fill in some integer values in column A (starting at Row 2 as set in my
code), press Alt+F8 and select Distribute1sAnd0s from the list, and click
the Run button.

Rick

Sub Distribute1sAnd0s()
Const DataStartRow As Long = 2
Const DataStartCol As String = "A"
Const BinStartColumn As String = "C"
Const WorkSheetName As String = "Sheet1"
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim BinValue As String
With Worksheets(WorkSheetName)
LastRow = .Cells(Rows.Count, DataStartCol).End(xlUp).Row
For X = DataStartRow To LastRow
BinValue = Dec2Bin(.Cells(X, DataStartCol).Value, 16)
For Z = 1 To Len(BinValue)
.Cells(X, DataStartCol).Offset(0, Z + 1).Value = Mid(BinValue, Z, 1)
Next
Next
End With
End Sub

Function Dec2Bin(ByVal DecimalIn As Variant, _
Optional NumberOfBits As Variant) _
As String
Dec2Bin = ""
DecimalIn = CDec(DecimalIn)
Do While DecimalIn <> 0
Dec2Bin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & Dec2Bin
DecimalIn = Int(DecimalIn / 2)
Loop
If Not IsMissing(NumberOfBits) Then
If Len(Dec2Bin) > NumberOfBits Then
Dec2Bin = "Error - Number too large for bit size"
Else
Dec2Bin = Right$(String$(NumberOfBits, "0") & _
Dec2Bin, NumberOfBits)
End If
End If
End Function

There is DEC2BIN function available? Where is it (I'm getting a #NAME? error
when I try your formula)?

Rick


"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 27 Aug 2008 12:16:05 -0700, DCLittlejohn
> <(E-Mail Removed)> wrote:
>
>>I would like to take a 16-bit integer value, convert it to binary and then
>>break the individual 1s and 0s into seperate columns.
>>
>>For example, a value of 5 in column A to break out into columns C,D,E,F as
>>0,1,0,1 respectivly.
>>
>>I need to be able to use the individual 1s and 0s later in the sheet.
>
> A1: 5
> C1: =MID(DEC2BIN($A$1,4),COLUMNS($A:A),1)
>
> Fill right to F1
>
>
>
> --ron

Analysis ToolPak

--


Regards,


Peo Sjoblom

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> There is DEC2BIN function available? Where is it (I'm getting a #NAME?
> error when I try your formula)?
>
> Rick
>
>
> "Ron Rosenfeld" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> On Wed, 27 Aug 2008 12:16:05 -0700, DCLittlejohn
>> <(E-Mail Removed)> wrote:
>>
>>>I would like to take a 16-bit integer value, convert it to binary and
>>>then
>>>break the individual 1s and 0s into seperate columns.
>>>
>>>For example, a value of 5 in column A to break out into columns C,D,E,F
>>>as
>>>0,1,0,1 respectivly.
>>>
>>>I need to be able to use the individual 1s and 0s later in the sheet.
>>
>> A1: 5
>> C1: =MID(DEC2BIN($A$1,4),COLUMNS($A:A),1)
>>
>> Fill right to F1
>>
>>
>>
>> --ron
>

I thought that might be the case; however, I have the Analysis ToolPak
add-in checked on my system and I am getting the #NAME? error. Any idea why
that may be happening?

Rick


"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Analysis ToolPak
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:%(E-Mail Removed)...
>> There is DEC2BIN function available? Where is it (I'm getting a #NAME?
>> error when I try your formula)?
>>
>> Rick
>>
>>
>> "Ron Rosenfeld" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> On Wed, 27 Aug 2008 12:16:05 -0700, DCLittlejohn
>>> <(E-Mail Removed)> wrote:
>>>
>>>>I would like to take a 16-bit integer value, convert it to binary and
>>>>then
>>>>break the individual 1s and 0s into seperate columns.
>>>>
>>>>For example, a value of 5 in column A to break out into columns C,D,E,F
>>>>as
>>>>0,1,0,1 respectivly.
>>>>
>>>>I need to be able to use the individual 1s and 0s later in the sheet.
>>>
>>> A1: 5
>>> C1: =MID(DEC2BIN($A$1,4),COLUMNS($A:A),1)
>>>
>>> Fill right to F1
>>>
>>>
>>>
>>> --ron
>>
>
>

I works for my with Excel 2000.

It might be spelled wrong.

Does the function show up in the list of functions when you type an
"=" in a cell?

Strange... I closed Excel down and then started it back up again and,
"Voila!", there it is. I'm not sure what glitch happened to kill it off but
restarting Excel straightened it out.

Rick


"dan dungan" <(E-Mail Removed)> wrote in message
news:b510f0dc-0f3b-4a7c-b35c-(E-Mail Removed)...
>I works for my with Excel 2000.
>
> It might be spelled wrong.
>
> Does the function show up in the list of functions when you type an
> "=" in a cell?
>
>
>
>

I think you still have to answer the 16-bit versus 4-bit question, but you
don't need to use my function unless you want to do this via a macro... for
a worksheet formula solution, load the Analysis Toolpak add-in (assuming you
don't have it already loaded or you are using XL2007), it has a DEC2BIN
function built in to itself, the see Ron's first post in this thread for how
to use it (still subject to that 16 versus 4 bit question).

Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> >I would like to take a 16-bit integer value, convert it to binary and
> >then
>> break the individual 1s and 0s into seperate columns.
>>
>> For example, a value of 5 in column A to break out into columns C,D,E,F
>> as
>> 0,1,0,1 respectivly.
>>
>> I need to be able to use the individual 1s and 0s later in the sheet.
>
> Your example shows 0101 as the value of 5... technically, that is correct,
> but it is *not* a 16-bit binary value (it's a 4-bit binary value)... the
> 16-bit binary values would be 0000000000000101. So you will need to
> clarify what exactly you want returned. In anticipation that your 16-bit
> statement was correct (and 0101 was due to hurried typing), you can use
> the VBA macro (which relies on the accompanying function) after my
> signature to populate the cells you indicated you wanted filled in. Simply
> change the values being assigned in the Const statement (if they end up
> being different from what posted) as necessary (the WorksheetName constant
> definitely needs to be looked at to see if you need to).
>
> If you are not familiar with macros and functions, you would implement my
> code by going into the VBA editor (press Alt+F11 from any worksheet),
> click Insert/Module from the editor's menu bar, and copy paste all the
> code after my signature into the code window that opened up. Now, go back
> to the worksheet you want to have this functionality on (Sheet1 as set in
> my code), fill in some integer values in column A (starting at Row 2 as
> set in my code), press Alt+F8 and select Distribute1sAnd0s from the list,
> and click the Run button.
>
> Rick
>
> Sub Distribute1sAnd0s()
> Const DataStartRow As Long = 2
> Const DataStartCol As String = "A"
> Const BinStartColumn As String = "C"
> Const WorkSheetName As String = "Sheet1"
> Dim X As Long
> Dim Z As Long
> Dim LastRow As Long
> Dim BinValue As String
> With Worksheets(WorkSheetName)
> LastRow = .Cells(Rows.Count, DataStartCol).End(xlUp).Row
> For X = DataStartRow To LastRow
> BinValue = Dec2Bin(.Cells(X, DataStartCol).Value, 16)
> For Z = 1 To Len(BinValue)
> .Cells(X, DataStartCol).Offset(0, Z + 1).Value = Mid(BinValue, Z,
> 1)
> Next
> Next
> End With
> End Sub
>
> Function Dec2Bin(ByVal DecimalIn As Variant, _
> Optional NumberOfBits As Variant) _
> As String
> Dec2Bin = ""
> DecimalIn = CDec(DecimalIn)
> Do While DecimalIn <> 0
> Dec2Bin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & Dec2Bin
> DecimalIn = Int(DecimalIn / 2)
> Loop
> If Not IsMissing(NumberOfBits) Then
> If Len(Dec2Bin) > NumberOfBits Then
> Dec2Bin = "Error - Number too large for bit size"
> Else
> Dec2Bin = Right$(String$(NumberOfBits, "0") & _
> Dec2Bin, NumberOfBits)
> End If
> End If
> End Function
>

On Wed, 27 Aug 2008 15:58:20 -0400, "Rick Rothstein \(MVP - VB\)"
<(E-Mail Removed)> wrote:

>There is DEC2BIN function available? Where is it (I'm getting a #NAME? error
>when I try your formula)?
>
>Rick

I see you figured it out. I have 2007 and had forgotten that Dec2Bin was part
of the ATP.

I, too, have had occasions where restarting Excel re-enabled the ATP. No idea
why, either.
--ron