cell formatting zero padding on binary no's


I'm struggling through doing some binary math on excel.  Ugh.  You'd think
Excel is written by programmers, so they would see the value of having
some programmer-friendly bitwise math and binary, octal and hex formatting
stuff in there, if for no other reason than they could use it themselves.

Anyway, that's my rant, here's my question


I have some numbers in cells that I convert to binary

   C5: 55       DEC2BIN(C5) gives me 1010101

I'd really like to display that as 

       0101 0101

.... ie with a leading zero and a space between the nibbles.


It appears that cell formatting (for example which which allows me to show
a number 357 as 00035) does not work with binary numbers! Or am I doing
something wrong.   I went into cell format and made a custom format of 

  0000 0000

and it doesn't do anything for me.  When I apply that same formatting to a
number like 286 it becomes:

  0000 0286

which is what I expect it to do.  Any way to make that work for binary?


Thanks in advance....


Ross.
0
Spam9694 (2)
3/10/2005 6:06:21 PM
excel 39879 articles. 2 followers. Follow

3 Replies
589 Views

Similar Articles

[PageSpeed] 45

maybe something like:

=TEXT(--DEC2BIN(C5),"0000 0000")

The -- coerces the text to a number.

RossK wrote:
> 
> I'm struggling through doing some binary math on excel.  Ugh.  You'd think
> Excel is written by programmers, so they would see the value of having
> some programmer-friendly bitwise math and binary, octal and hex formatting
> stuff in there, if for no other reason than they could use it themselves.
> 
> Anyway, that's my rant, here's my question
> 
> I have some numbers in cells that I convert to binary
> 
>    C5: 55       DEC2BIN(C5) gives me 1010101
> 
> I'd really like to display that as
> 
>        0101 0101
> 
> ... ie with a leading zero and a space between the nibbles.
> 
> It appears that cell formatting (for example which which allows me to show
> a number 357 as 00035) does not work with binary numbers! Or am I doing
> something wrong.   I went into cell format and made a custom format of
> 
>   0000 0000
> 
> and it doesn't do anything for me.  When I apply that same formatting to a
> number like 286 it becomes:
> 
>   0000 0286
> 
> which is what I expect it to do.  Any way to make that work for binary?
> 
> Thanks in advance....
> 
> Ross.

-- 

Dave Peterson
0
ec357201 (5290)
3/10/2005 6:34:03 PM
Hey, that works nicely.  Didn't know about the -- operator.

Thanks for that!

Ross.




In article <4230931B.F3F8CB3D@netscapeXSPAM.com>,
ec35720@netscapeXSPAM.com wrote:

> maybe something like:
> 
> =TEXT(--DEC2BIN(C5),"0000 0000")
> 
> The -- coerces the text to a number.
> 
> RossK wrote:
> > 
> > I'm struggling through doing some binary math on excel.  Ugh.  You'd think
> > Excel is written by programmers, so they would see the value of having
> > some programmer-friendly bitwise math and binary, octal and hex formatting
> > stuff in there, if for no other reason than they could use it themselves.
> > 
> > Anyway, that's my rant, here's my question
> > 
> > I have some numbers in cells that I convert to binary
> > 
> >    C5: 55       DEC2BIN(C5) gives me 1010101
> > 
> > I'd really like to display that as
> > 
> >        0101 0101
> > 
> > ... ie with a leading zero and a space between the nibbles.
> > 
> > It appears that cell formatting (for example which which allows me to show
> > a number 357 as 00035) does not work with binary numbers! Or am I doing
> > something wrong.   I went into cell format and made a custom format of
> > 
> >   0000 0000
> > 
> > and it doesn't do anything for me.  When I apply that same formatting to a
> > number like 286 it becomes:
> > 
> >   0000 0286
> > 
> > which is what I expect it to do.  Any way to make that work for binary?
> > 
> > Thanks in advance....
> > 
> > Ross.
0
Spam9694 (2)
3/10/2005 7:22:58 PM
Ross,

The interesting thing about this is that 101 0101 isn't the binary for 
decimal 55.  The binary for 55 decimal is 110111.  55 hex is 0101 0101 in 
binary, though.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"RossK" <Spam@nothanks.ru.kidding> wrote in message 
news:Spam-1003051306210001@localhost...
>
>
> I'm struggling through doing some binary math on excel.  Ugh.  You'd think
> Excel is written by programmers, so they would see the value of having
> some programmer-friendly bitwise math and binary, octal and hex formatting
> stuff in there, if for no other reason than they could use it themselves.
>
> Anyway, that's my rant, here's my question
>
>
> I have some numbers in cells that I convert to binary
>
>   C5: 55       DEC2BIN(C5) gives me 1010101
>
> I'd really like to display that as
>
>       0101 0101
>
> ... ie with a leading zero and a space between the nibbles.
>
>
> It appears that cell formatting (for example which which allows me to show
> a number 357 as 00035) does not work with binary numbers! Or am I doing
> something wrong.   I went into cell format and made a custom format of
>
>  0000 0000
>
> and it doesn't do anything for me.  When I apply that same formatting to a
> number like 286 it becomes:
>
>  0000 0286
>
> which is what I expect it to do.  Any way to make that work for binary?
>
>
> Thanks in advance....
>
>
> Ross. 


0
nothanks4548 (968)
3/10/2005 11:20:18 PM
Reply:

Similar Artilces:

Headers based on Cell Values
I would like to have my header variable based upon the value of certai cells. If there is a way to do this without VBA, that would be the best way However, if it is only in VBA, then that is what I will have to do -- kralj ----------------------------------------------------------------------- kraljb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=995 View this thread: http://www.excelforum.com/showthread.php?threadid=27791 Hi not possible without VBA. You have to use the BeforePrint event of your workbook. So try putting the following type of code in your wo...

Formatting currency within a report text box expression
I'm trying to create a mail merge letter from a 2007 ACCB database using a report (Word can't cope with the filter) To keep the text flowing to allow for expansion of various field values, I've put the following in a text box. ="According to my records you " & [Expr2] & " " & [KEYNO] & ", have made a total payment of " & [PAID] & " comprising a permit fee of " & [PERMIT£] & " and key deposit of " & [KEY£] & ". Therefore " & [Expr1] Ignoring Expr1 &...

How can I retrieve the format of a cell?
Dear all, A cell A1 is conditionally formatted so that it is either red (FF0000), green (00FF00) or blue (0000FF). May I know if I can write a function for another cell B1 such that it shows a word "RED" if A1 is red, a word "BLUE" if A1 is blue and a word "GREEN" if A1 is green? Thanks in advance. Best Regards, Chris You can use programming to determine the colour. Chip Pearson has instructions on his web site: http://www.cpearson.com/excel/CFColors.htm Chris wrote: > Dear all, > > A cell A1 is conditionally formatted so that it ...

FRx 6.7 Sp 10-no separators in row format
Hi Folks I've installed GP 10 Sp 4 and FRx 6.7 SP 10. When I try to 'add rows from chart of accounts' and I use the 'All&&&' function on 4 of my 9 segments ,the system will bring those account numbers into the row, but it does not put the hyphen as a separator. The hyphen has been defined as my separator in my Dynamics GP account format set up. I've also checked in FRx under 'Admin/System Preferences' that the checkbox for using the hyphen as the space indicator is ticked. I've ticked and unticked this box and it makes no difference....

Merge Cell Problem
I have created a new worksheet which is very basic with no formulas. I want to merge some cells however the Merge' button on the tool bar is grey'd out. It does not function. I have never come across this before and do not know how to fix. Does anyone have any suggestions please. Any reply appreciated. Les Jetrascal Sounds like your worksheet is protected. Check tools>Protection, if there is an option to "Unprotect" you have a protected worksheet. You need to have an unprotected sheet to make this kind of change. Another option rather than merging ...

copy and pasting in 5000 records referencing cells
I have a file that is 5000 records long. example Column A Column B namea nameb namec Transit number associated with (a,b,c) What I want is to have transit number appear in Column a beside each of the individuals. The issue is the transit number is different for all 5000 records some may have 5 per transit number and others just 3 and some have 7 depending on size of the branch. Is there a formula to populate this without copy and pasting in each cell? thanks Yes "Fawn Lagi...

Delete an image from the cell
Delete an image from the cell Hi I am transforming html report content (which is generated using XML and XSLT) into Excel wroksheet. I opened the excel file and delete the image from the cell but the excel does not allow to delete an image. I could not able to find the properties of the cell. How could i delete the image from the cell? Thanks Yuva. Cells can contain constants, formulae, or errors. They cannot contain images/shapes. Images exist in the drawing layer above the sheet, and can be accessed via the shapes or pictures collection. In article <B6561E41-1068-477B-99E3-A...

Custom Cell Formatting #2
Okay, I know you can enter in @*- in the custom formatting for continued dashes across the cell, Invoice--------- but what if I want dashes on both side of the text? For example, ----------Invoice---------- Can this be done in the custom format or do I have to manually enter in all the dashes? Any help is much appreciated. Corey Corey I don't know of a format that will do this. If you have a great whack of these to do you could enter a few dashes in a cell by itself, say A1 has ------- Assuming Column B contains Invoice or any other non-custom formatted text. In C1 enter =$...

Transfer cell data from one worksheet to another === URGENT
bello ALL, I do I transfer a cell data from one worksheet in Workbook A to another cell in another worksheet in Workbook B. Thanks, Raj Please stay in your thread You have an answer there already -- Kind regards, Niek Otten "rajesh" <rajesh@discussions.microsoft.com> wrote in message news:0DFD6622-7C55-4E29-9D2A-7E3F7B4BD2DC@microsoft.com... > bello ALL, > > I do I transfer a cell data from one worksheet in Workbook A to > another cell in another worksheet in Workbook B. > > Thanks, Raj ...

Help, with formatting issue?
Under Outlook in tool/options / mail format, if I have it set to plain text and I create new mail, I do not have the ability to select formatting options. Try it, it shows up under customize ok, as soon as you select ok under customize, it blanks out. If I choose to use Microsoft Outlook Rich Text, I can't send some attachments to Outlook express users. Any one able to confirm this? Its driving me nuts.... -- Greg Eshleman ETEMCO 1370 Arcadia Road Lancaster, PA. 17601 717-393-9653 http://www.etemco.net geshlema@etemco.net What about using HTML format instead? It's open st...

losing date format when referencing date cell on another sheet and
I need to reference a date cell in another sheet in my workbook. I select the cell that needs to display the referenced content. Then in the formula bar I type = . Then I move to the sheet containing the cell I want to reference. I select this cell and then click the green checkmark next to the formula bar. The desired date value now shows up in my display cell in the correct format. But I need to add text to this cell. Now my display date loses its format and becomes a plane number. Here is my reference =Sheet1!B15 Here is what I want to do = "(" & She...

Search within a single cell for a character
Using VBA I want an If/Then statement to search within a single cell (D59) to find if the ":" (colon character) exists after the fourth character. If not "Then: Wrong = True" In an adjacent worksheet cell I could just enter =iserr(find(":",D59,4)) and if the result is True then I know it doesn't exist. How do I write this in VBA? If Instr(5, Range("D59"), ":") > 0 then Wrong = True "AZSteve" wrote: > Using VBA I want an If/Then statement to search within a single cell (D59) to > find if the ":&...

how do I scan a document and save it to a JPEG format.
I am try to scan a document to upload to a web site. I have ms publisher,word xp and acrobat reader.They tell me that I need to save it to JPEG format. How do I do that Publisher isn't really the tool to do this in. What software came with = your scanner? It's musta came with some basic image thingie...most do. "carole" <carole@discussions.microsoft.com> wrote in message = news:0FA226DD-CF51-46FE-B308-49C77C89A506@microsoft.com... | I am try to scan a document to upload to a web site. I have ms = publisher,word=20 | xp and acrobat reader.They tell me that I need t...

VBA format function codes
I thought I understood the VBA Format function codes, but apparantly not. I have several columns of numbers that I want to write to a file so that they line up. Some of them are integers, some have a few decimal places. For the integers, I first tried "####0": ?format(123,"####0") & "|" 123| I forgot that "#" does not insert spaces. Next I tried "@@@@0": ?format(123,"@@@@0") & "|" 1230| Now I get the spaces, but why am I getting the extra zero? Next I tried "@@@@@": ...

Cannot permanently remove formatting toolbar
In Outlook 2003 mail send I have the formatting toolbar visible. This then hides other icons e.g. attach. I can use tools/customise to remove the formatting bar but this does not stick and on opening a new send mail the formatting bar has returned. How do I permanently remove it? Beemer Beemer <Beemer@nowhere.com> wrote: > In Outlook 2003 mail send I have the formatting toolbar visible. > This then hides other icons e.g. attach. I can use tools/customise > to remove the formatting bar but this does not stick and on opening a > new send mail the formatting bar ha...

Highlight rows with certain condition in some cells
I want to highlight every row that has a certain word in one column and a different word in another column of the same row. How do I do this? SteveK Here's a great site to learn about conditional formatting :) http://www.mvps.org/dmcritchie/excel/condfmt.htm -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31708 View this thread: http://www.excelforum.com/showthread.php?threadid=520844 "SteveK" wrote: > I want to highlight every row that h...

Formatting day of the week
I know that formatting a date with ddd shows the first three letters of the day, but is there a way to show only the first letter of the day (Mon = M, Tue = T)? TIA, -- Jordon You could use another column with a formula like: =left(text(a2,"ddd"),1) Jordon wrote: > > I know that formatting a date with ddd shows the first three letters > of the day, but is there a way to show only the first letter of the > day (Mon = M, Tue = T)? > > TIA, > > -- > Jordon -- Dave Peterson That saved me a bunch of work. Thanks Dave! -- Jordon On Dec 26, 10:27 am,...

Setting a cell to not go below an X value, or above a Y value
Hey, This may be very easy to do, but I just don't have a clue, I've look a most excel variables and they just seem to choose MIN and MAX fro columns or tables. What I want to do is I've got the sum adding up say cells A2 and A3 i cell A4. I want cell A4 to never go below 80, or above 200. (Formula in cell A would be =SUM(A2:A3)) Anyone think they can help -- Snake ----------------------------------------------------------------------- Snakey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2785 View this thread: http://www.excelforum.com/showthre...

How to make the cursor stay in the cell when entering data.
I do not want the cursor to go to the cell immediately below the cell I have entered data in. I want the cursor to stay in the cell. Tools > Options > Edit > Uncheck Move selection after entry > OK HTH Regards, Howard "Comet" <Comet@discussions.microsoft.com> wrote in message news:B6EF27C0-4F12-4E72-9FAE-93B79515AB1F@microsoft.com... >I do not want the cursor to go to the cell immediately below the cell I >have > entered data in. I want the cursor to stay in the cell. Tools - Options - Edit tab - uncheck the box "Move selecti...

Excel 2002 : How to eliminate _ before a cell address in a formula
Hi, I am working on a report generated by a business system In Excel Format. The table in the Excel file is a Sub Total Table that is expandable. I need to manipulate on the data of the expanded table, however noted that the formula below could not work as I copy downwards. The second and third argument of the formula is fixed by "_" before the cell address E17 and D17. =IF(LEFT(P10,1)="5",_E17,-_D27) Also I find that when I move the cursor to the cell for the 2nd and 3rd argument, the resulting cell address that appears in the formula is not the...

Conditional Formatting
I'm trying to use a simple "less than" condition, but Access keeps interpreting the comparison field as a text string instead of a number, causing strange results; e.g. 5>11 Any quick solutions? (I want to avoid assigning leading zeros if possible) First of all less than is < not >. Conditional formatting is based on a fields value. If you have a totals field called total and you want all totals > than 500 to be red, then you set the formatting of that field to red. Such as: Field Value Is greater than 500; then select the color blue from the 'A' drop-...

Formatting Cells #16
Hello, I am currently using Office 2003 - OS - Windows XP I was just approaches with a question pertaining to Excel and thought I would also direct to you because at this point I am unsure of the answer. Is it possible to automatically make a cell have 10 spaces and nothing else? For example, if you input a student’s name with only 6 letters, Excel will automatically add 4 spaces after their name. Also, if the student’s name has 11 characters, it will only allow 10 characters to be inputted. Thanks in Advance. Hi, If the names are in column A starting at A1, create a helper col...

access a cell in a range
I have variables: dim myrange as Range dim myString as String myrange specifies a specific column. How can I specify the cell in row 1 of myRange so I can set it's contents to myString? Hi there keyser soze, (Fan of the movie, eh? ;) <g> ) Like so .. myString = myRange(1).Value HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) <keyser_Soze@usa.com> wrote in message news:1128697509.320303.12550@g49g2000cwa.googlegroups.com... >I have variables: > > dim myrange as Range > dim myString as String > > myrange specifies a specific ...

Track changes to cell formats
Is there any way (or workaround) to track changes made to cell formats, such as number of decimals displayed, bolding, etc. I really need to know which formats have been changed. Any help you can give me would be greatly appreciated. Trish I think I'd use another worksheet that was formatted exactly the way I wanted it. (maybe hide that sheet). Then I could compare that base sheet's format with the other sheet's current format. Trish wrote: > > Is there any way (or workaround) to track changes made to > cell formats, such as number of decimals displayed, > ...

Date format on checks
How do you change the date format on checks? Money 2005 is using a day/month/year format and several payees have complained that 1/5/05 means 1 May 2005 and their banks are objecting. Have you tried messing with Region Settings for the operating system? "ROBERT L GUTHRIE" <rlguthrie@mindspring.com> wrote in message news:tcXHd.2206$cZ1.904@newsread2.news.atl.earthlink.net... > How do you change the date format on checks? Money 2005 is using a > day/month/year format and several payees have complained that 1/5/05 means 1 > May 2005 and their banks are objecting....