EXCEL 2007 and TEXT formatting not remembering after a capy/ paste

[DougP]

I am adding and subtracting binary numbers. I hope someone can tell me I can do something to “force” Excel it to behave nicely.
OK I type in 0010010 but if the cell is not formatted TEXT it converts it to 10010 dropping the leading zeros. if I have 10010 in the cell it dose not bring back the dropped zeros. I have to delete the text and re key it in. I format it text and I get the result. but guess what if I copy a number from some where else like a WEB site where the test is and paste it in Excel it forgets the TEXT formatting and drops the leading zeros again. I don’t time have to fiddle fart with it when I have 10 minutes to do 10 problems and not get 3 wrong OK. 3 wrong is an D and every minute after ten minutes is a point off making it an F and then before you have time to say Oh S___ you have a 0 for the test.






DougP
< I Built one

http://www.robots2000.com

 

[xlhelp]

If you know you have fixed number of digits, highlight your cells where you want the leading Zeros to appear. On the Home tab, in the Numbers group click on the arrow beside the word Number. Then, under the Number tab click Custom; and in the "Type:" box type in Zeros for the length you wish.

If the number is going to be variable length, then you can add leading Zeros by custom formatting as "000"# where it will add three zeros to every number. I doubt if this second method will be useful to you.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[AnotherHiggins]

xlhelp: That will only change the appearance of the data in the cell. You'll notice that the actual value in the formula bar is still missing the leading zeros.

DougP: If you just need to display the leading zeros but not actually store those leading zeros, then a custom format will work. But you said the problem you're running into is

if I copy a number from some where else like a WEB site where the test is and paste it in Excel it forgets the TEXT formatting and drops the leading zeros

So just [!]Right Click > Paste Special > Text[/!] (or, if you're into keyboard shortcuts, [!][Alt], [E], , [T], [Enter][/!])



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I just read through my reply again and realized that I failed to make this point:

The problem you're running into is that when you paste into Excel from the web, whatever format you've already applied to the destination cells is being overwritten and changed back to General.

You'll have that same problem regardless of what format you try to pre-apply to the cells before pasting from the web.

The key to keeping the leading zeros in place is Paste Special > Text.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.