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

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.