Forum Discussion
Copy and paste OR import trouble with exponential numbers
Dear Sergei,
Thank you for your response. I discovered the problem, I use my computer with a , for decimals and not . as in the american system. When I changed it it works just fine. Best regards.
So far so good, thank you for the update
I am facing the same problem, when I paste
-4.424312e-003 in excel it gets translated to -4.42E3
Yes: This is __not__ a formatting problem, as SergeiBaklan suggested. He overlooked the fact that the power of 10 changed from -3 to +3.
Instead, it is a problem with the interpretation of period and comma as thousands separator and decimal point, as José Maurício Gonçalves dos Santos suggests.
alyna47, to reiterate and clarify.... The problem is that when you enter -4.424312e-003 in your environment, the period is interpreted as a thousands separator, not a decimal point. Presumably, you expect the latter.
So Excel correctly inserts the decimal point before "312" because "E-03" is like "divide by 1000". And it displays -4.42E+03 (sic; -4,42E+03?) by default because Excel interprets the number as -(4424 + 312/1000).
-----
But __I__ do not understand José Maurício Gonçalves dos Santos's suggestion, to wit: ``paste the numbers in decimal not in exponential, and then convert to exponential``.
Please explain in more detail, step-by-step and perhaps with some images, if only for my edification. TIA.
The only interpretation that makes sense to me is: paste -4424,312 (comma decimal point), then format as Scientific. But that presumes that alyna47 has access to the numbers in that form. I doubt that is the case.
IMHO, a more straight-forward work-around is to temporarily select and change the Excel option "Use systems separators", paste the numbers exactly as they are (-4.424312e-003, for example), then deselect the "Use systems separators" option.
In Excel 2010, I find the option by clicking File > Options > Advanced. Initially, it might look something like this:
Click to remove the checkmark, change "Decimal separator" to period and "Thousands separator" to comma, then click OK. It might look something like:
After copy-and-pasting, click to restore the checkmark.
By default, the pasted values should now appear as -4,42E-03, for example.
As SergeiBaklan suggests, change the cell format if you wish to see more precision, -4,424312E-03 for example.
- Hi Joe.
If possible, use the numbers like -0.00443 and then in Excel you can format to exponential and then you will get -4.43E-3. Best regards.
Hi, the problem still remains. If you paste the numbers in decimal not in exponential, and then convert to exponential, it will works. When I had this problem few years ago, I solved it changing the format of the number on the computer settings to UK or USA (, for thousand and comma for decimal). I tested you number in my current configuration and the result was the same you had.
Excel shows in default format. You may apply any one you prefer, for example
, value will be the same.
