This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc. 

Try our online
PLC Simulator FREE.
Click here now to try it.

New Here? Please read this important info!!!
December 22nd, 2008, 08:09 PM  #1 
Member

Excel code for breaking values into bit fields
I'm looking for a fast easy Excel code to move whole integer values into separate bit fields. Does anyone know of a relatively easy code?

December 22nd, 2008, 08:34 PM  #2 
Member

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

December 22nd, 2008, 08:35 PM  #3 
Lifetime Supporting Member + Moderator

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. 
December 22nd, 2008, 11:20 PM  #4 
Lifetime Supporting Member

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 LLSOLVER 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
__________________
True craftsmanship is only one more power tool away. That's the beauty of processors, they don't have emotions they just run code  The PLC Kid. Last edited by TConnolly; December 22nd, 2008 at 11:24 PM. 
December 23rd, 2008, 11:34 AM  #5 
Member

This is a great code. Thanks!

December 23rd, 2008, 12:28 PM  #6 
Member
Join Date: Dec 2008
Location: Houston
Posts: 3

A different approach  no vba
abcdefghijklmnopq1bit151413121110987654321023276816384819240962048102451225612864321684213111111111111111111111111878787872323773104=if(b6=1,a3b2,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 
December 23rd, 2008, 12:29 PM  #7 
Member
Join Date: Dec 2008
Location: Houston
Posts: 3

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

December 23rd, 2008, 12:33 PM  #8 
Member
Join Date: Dec 2008
Location: Houston
Posts: 3

another try
another way to skin the cat

December 23rd, 2008, 01:44 PM  #9 
Member

And this is my code which I use for a long time.
Code:
Function KM(ByVal H As Long, L As Long) As String ' this function returns binary sequence as string ' H  input integer value ' L  length of returned string Dim k As Long Dim E As Long KM = "B" 1: E = H Mod 2 If E = 0 Then KM = "0" + KM Else KM = "1" + KM End If H = Int(CDbl(H) / 2) If H >= 1 Then GoTo 1 k = 1 + L  Len(KM) While k <> 0 KM = "0" + KM k = k  1 Wend End Function 
December 23rd, 2008, 01:59 PM  #10 
Member

this is complete Excel book with function above
ps  I'm sorry for russian comments 
December 23rd, 2008, 04:49 PM  #11 
Lifetime Supporting Member

So far we have 100 ways to skin that cat, need to make it 101 just to be cliche.
__________________
True craftsmanship is only one more power tool away. That's the beauty of processors, they don't have emotions they just run code  The PLC Kid. 
December 24th, 2008, 07:27 AM  #12 
Member
Join Date: Jun 2006
Location: swindon
Posts: 40

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.

Bookmarks 
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)  
Thread Tools  
Display Modes  


Similar Topics  
Thread  Thread Starter  Forum  Replies  Last Post 
Alarm Code  mwatkins  LIVE PLC Questions And Answers  27  October 15th, 2019 03:44 AM 
Automatin Direct Changes Download  ArikBY  LIVE PLC Questions And Answers  53  May 12th, 2004 01:05 AM 
Internal addresses  rogerhollingsworth  LIVE PLC Questions And Answers  5  August 10th, 2003 07:02 PM 
Looping VBA code in Excel  pagewe  LIVE PLC Questions And Answers  8  February 10th, 2003 11:19 AM 
Auto generate code in Excel?  PhilRey  LIVE PLC Questions And Answers  9  January 17th, 2003 07:32 AM 