Excel Format Problem

Hi, I'm having a problem copying and pasting something from the internet.  When I paste, I use "Paste Special" and pre-select text for this column.  Sometimes it works and at others it doesn't.
Here is how it appears on my worksheet when it gets pasted; 2.01002E+13
Here is how it appears above in the address bar when I click on the cell: 20100187407610
If I copy the number from above and try to paste it in the cell it rejects it and gives me the same result;  2.01002E+13.  If I click on the cell and try to format it it gives me a font to choose from instead of the usual format window. What can I do to get correct the cell once it has gotten screwed up?
Who is Participating?
bromy2004Connect With a Mentor Commented:
If the original data was from excel (i.e. excel to excel) you would have the Formulas/Values options

The HTML, Unicode, Text options are from non-excel programs.
Paste Values is the same as Paste Text.

It would then change the format to what the cell is.
if you paste-text into a date column, then it would be formatted as date.

So you need to change the format to number with 0 decimal places,
and the paste as text
barry houdiniCommented:
Font option may appear by default when you try to format but you should see other tabs -select the "Number" tab. You should be able to click on that then choose formatting as "Number" and set to zero decimal places,
regards, barry
2.01002E+13 is called scientific notation... 20,0000 is the same as  2.0 E+5

Before you past in excel, format the sheet as TEXT...Then Paste-Special --->Values.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Rory ArchibaldCommented:
The Font tab will be the only tab visible if you are in edit mode. Press Enter then right-click the cell again and choose Format Cells, and set the number format.
camtzAuthor Commented:
If you paste the numbers as text, can you still calculate the numbers? Like 12345 times 6789
Rory ArchibaldCommented:
You don't need to paste as text, you just need to change the cell formats.
camtzAuthor Commented:
sublimation:, When I try to use the Paste Special button, I get a window that says;
Paste   As
            Unicode Text
That's it.

However, I remember seeing the other window that gives you many choices (including values).  Wonder why it doesn't come up when I try to do this operation.
camtzAuthor Commented:
Also, I clicked on the Paste icon arrow and I see various listings such as; Formulas, Values, No borders, etc. and they are all grayed out (meaning I can't select them). The reason this is so important is that once I paste all the data, I need to do further calculations.
All Courses

From novice to tech pro — start learning today.