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?
camtzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
0
sublimationCommented:
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.
0
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

camtzAuthor Commented:
If you paste the numbers as text, can you still calculate the numbers? Like 12345 times 6789
Thanks.
0
Rory ArchibaldCommented:
You don't need to paste as text, you just need to change the cell formats.
0
camtzAuthor Commented:
sublimation:, When I try to use the Paste Special button, I get a window that says;
Source
Paste   As
            HTNL
            Unicode Text
            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.
0
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.
0
bromy2004Commented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.