Scientific notation: remove this "feature" from Excel!

Question

Please allow users to paste phone numbers, without converting it to scientific notation. It's a shame.

Who need it? How many years people should "hack" Excel to paste numbers?

See this SuperUser post to all the pain you do to users just for a simple Ctrl+V.

I tried to format cells as text, I removed all cells, selected all cells, put Formatting, Number=>Text. After that pasted.

I saw my big numbers as bellow in ... scientific format... I hate now the scientific format.

tried it before the paste however that does not help, tried after the paste, does not help.

This is a bug: the formatted as text column should not convert anything to anything!

paste this one 1837503030608800000

If I convert this as a number, it will remove all the spaces, I don't want my spaces be removed! I want my text as it is initially.

I never in my live needed the scientific notation, however I work in IT all my live.

The Excel is kidding on itself

old complaints, https://superuser.com/a/413277/465922

any correction from MS for years !

Answer 1

We don't talk here about paste options, but of the fact, that a big number is represented in a scientific notation, and there it seem nothing to do about it in Excel actually. Please see the OP, but also the images I posted above.

There is any "correct" paste option, or another kind of option, that could help to keep that number as is, at least, if you know, please share, but I am not sure, cause even here... "no luck"

https://superuser.com/a/413277/465922

Answer 2

1.Set text format in excel

2.Copy the data in to notepad

3.Copy from notepad

4.Paste to excel

Answer 3

We don't talk here about paste options, but of the fact, that a big number is represented in a scientific notation, and there it seem nothing to do about it in Excel actually. Please see the OP, but also the images I posted above.

Images are not helpful and only lead to misinterpretations of what is actually happening during data processing. We need your file, or a sample file, to show you what kind of data you really have and what happens.

Unfortunately, explanations are lengthy and the technical details cannot be explained in 2 sentences.

Maybe a short video helps:

In order for you to understand this, you need to know that numbers are only processed up to 15 digits in Excel and almost all programs in the world. Scientific notation for numbers with more digits is also common.

The key point, and I know this is confusing, is that you can only represent larger numbers as text in Excel. And all other programs on this planet.

And unfortunately, the cell format is also involved here, which leads to even more confusion.

For us as humans, a number is a number because we see it on screen. But in Excel, a number on screen, can mean in fact that you have text OR a number in a cell.

And now you also need to know that changing the cell format does NOT affect the cell content.

A text remains as text, regardless of the cell format.

A number remains as number, regardless of the cell format.

Even if both look like a number to you.

Alright, are you still with me? If so upload you file here:
Microsoft Answers Community Public Request - Dropbox

Or upload it on any file hoster of your choice and post the download link here.

Then I'll look at your file and show you how it works.

Andreas.

Answer 4

After a closer look I can tell where your problem is and what you are doing wrong... IMHO.

What we see on your screenshot looks like a text, but is in fact there is a number in the cell with a special custom formatting. Step by step:

If you select a cell in your file and look into the formula bar you see the same as in this screenshot:

This is a number and if we count the significant digits (in A2) we can see that there are only 14. So there is no problem for Excel to handle this as number.

If we look at the cell format in your file, we see this:

But in fact you have a number in the cell and a number with more then 15 digits is shown in scientific format by default.

Important to understand:

Whether a number can be processed correctly in Excel depends on the significant digits!

Not from all digits of the number.

So what are significant digits?

If you look at any number and ignore all zeros and the decimal separator count the places from the first non-zero digit to the last non-zero digit.

Examples:

This number has 9 significant digits, no problem to have that as number in Excel:

Number: 0.00000000000123456789

Count :              123456789

This number has 19 significant digits and can not be used as number in Excel AS IS:

Number: 0.01000000000123456789

Count :    1234567890123456789

Excel can handle only 15 significant digits, therefore the number is truncated:

Number:  0.0100000000012345

Count :     123456789012345

That's the simplified technical explanation what happens if you paste a long number into Excel.

Now let's see what happens if you copy and paste:

If you simply copy&paste there is no issue, because the cell format is also copied! If that doesn't happen on your PC means there is some code working in the background that prevents that!

Otherwise you paste as numbers only, and in this case Excel shows the number as I explained above.

Does this help you understand how Excel works?

Andreas.

Answer 5

Please see this my new thread to have a detaled reproduction of the problem bellow

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-is-does-not-respect-strings-when-text-to/2151fae5-6b9a-4fe6-b474-831279647eab