cell formatting in excel 2010

[phoenixmark]

I need some help in cell formatting using excel 2010. Wen I copy a 12 digit number (i.e. 163589453609), and paste it in a cell, this is what is shown 1.5358E+11. To get the actual number to be shown, I have to to the function cell and put a ' in front of the number to get the full 12 digit number to be shown in the original cell. I tried what I think are all the choices in the format cell drop down menus. Any ideas? Thanks
Mark

 

[johnhoh]

format all cells using

right click > format > number
or
right-click > format > custom > 0

could also be that your pasting defaults are messed up. When you paste select paste special and make sure it is set to "all", so that both the cell number as well as the cell format gets pasted.

If you are still having issues post a couple screen shots

 

[phoenixmark]

Thanks for the suggestions. They work but I have to do it for each cell that displays like this. I even tried it by highlighting the complete column and trying this but the next number I add still shows up xxxxxE+11. If it helps, these are numbers I copy from ebay auctions to track pricing trends on items I'm interested in buying in case I locate these items into make sure the sellers price is in line with what they normally sell for. I don't know how to post a diredt screenshot, but did attach a Word document.

 

[johnhoh]

If it helps, these are numbers I copy from ebay auctions to track pricing trends on items

By default excel keeps BOTH the contents and the formatting of a cell being copied, and an external number like copying from ebay will copy over both the item number and its formatting. So that's the problem.

There are many ways to fix this but the easiest is to change your method just a little. Right now you are doing this:

a) on the ebay window you highlight the number > right-click > copy
b) on the excel window you left-click > paste

whereas doing this will solve your problem

a) on the ebay window you highlight the number > right-click > copy
b) on the excel window you left-click TWICE > paste > enter

the second left-click in excel puts you into cell edit mode which does NOT copy over the original formatting.

A second way to do this is: see that little icon on cell B2 in your screenshot? After selecting paste, click that icon which will bring up two options. Click the option that says "Match destination formatting"