Excel code for breaking values into bit fields

you can use DEC2BIN to get binary value, then just extract character or bit of interest using MID

This formula should work:
=MID(TEXT(DEC2BIN(123,8),"00000000"),X,1)

where 123 is the number (could be reference to another cell) and in this case 8 is the number of bits you're working with. X is the position of the binary digit you want to extract (counting from the left/MSB, not the right). In order for the DEC2BIN function to work, you need to have the Analysis Toolpak enabled.

The attached spreadsheet has a little bit of legerdemain in it in the form of a custom function in VBA.

The function is:

Public Function BITAND(x As Long, y As Long)
BITAND = x And y
End Function

and it is located in Module 1 on the VBA sheets. You will need to have Excel security set to allow macros - I recommend medium so that you get a prompt before opening.

With the custom function any integer can be bitwise ANDed with any other. By ANDing it with 2^n, where n is a bit position, we can determine if that bit is TRUE or FALSE, and return the value 1 or 0 with the Excel IF function.

Just another way of doing it without using TEXT functions. It handles unsigned integers.

Also there is a useful tool called LL-SOLVER that is a free download, you can get it from the dowload section at MRPLC, which is useful for converting and viewing 16 bit integers in hex, decimal, and binary - much simpler than EXCEL for the occasional quick binary conversion.

http://forums.mrplc.com/index.php?download=606

This is a great code. Thanks!

A different approach - no vba

abcdefghijklmnopq1bit151413121110987654321023276816384819240962048102451225612864321684213111111111111111111111111878787872323773104=if(b6=1,a3-b2,a3)56=c3,1,0)">0=d3,1,0)">0=e3,1,0)">0=f3,1,0)">0=g3,1,0)">0=h3,1,0)">1=i3,1,0)">0=j3,1,0)">0=k3,1,0)">0=l3,1,0)">1=m3,1,0)">0=n3,1,0)">1=o3,1,0)">0=p3,1,0)">1=q3,1,0)">1=r3,1,0)">17=b2,1,0)">=if(a3>=b2,1,0)


formula listed below the start of cell/row so visible in this copy. Formulas copied across b3 thru q3 and b6 thru q3. Number to parse goes in a3, row 6 gives bits. Formulas could probably be combined and row 2 numbers hardcoded into formula if you need to get to one line.

Mike

Well, copy from excel did not work at all. Will attach file.

another try

another way to skin the cat

And this is my code which I use for a long time.



I was late a little, but I will be glad if it be useful for someone.

this is complete Excel book with function above

ps - I'm sorry for russian comments

So far we have 100 ways to skin that cat, need to make it 101 just to be cliche.

Some more skinned cats

I normally use the ^ (power of) operator when decoding binary to/from decimal. There are three functions on the sheet, Decimal to binary,Binary to decimal and a function to check a bit status in a word.