Convert to Binary and Break out results

[DCLittlejohn]

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.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Peo Sjoblom]

Analysis ToolPak

--


Regards,


Peo Sjoblom

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

 

[dan dungan]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

There is DEC2BIN function available? Where is it (I'm getting a #NAME?

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.

That is the first time ATP ever went out on me (that I am aware of at
least). I thought I had remembered DEC2BIN as a function even though I never
had an occasion to use it before, but when I checked for it and didn't see
it, I figured I was remembering wrong. Anyway, the restart seems to have
straightened everything out although I would love to know what I did (or
what took place on my system) to knock it out.

Rick