Copy and paste OR import trouble with exponential numbers

Question

Hello,&nbsp;&nbsp; &nbsp; &nbsp;When I copy an number or import a TXT file with an negative exponential number (bellow) to excel3.03425675881e-05the number is altered to a positive exponential and even the exponential value was altered (bellow).&nbsp;3,03E+06&nbsp;Does anybody know how to solve this? I tried many options of copy (from a table of a docx file) and import directly from a txt file. On the conversion windows of the txt files the numbers are OK but when I conclude the procedure all the exponential numbers are altered. Thanks.

alyna47 · Answer

SergeiBaklan&nbsp;I am facing the same problem, when I paste&nbsp;-4.424312e-003 in excel it gets translated to -4.42E3

sergeibaklan · Answer

alyna47&nbsp;
Excel shows in default format. You may apply any one you prefer, for example

, value will be the same.

josé maurício gonçalves dos santos · Answer

alyna47&nbsp;&nbsp;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.

joeuser2004 · Answer

Yes:&nbsp; This is __not__ a formatting problem, as SergeiBaklan suggested.&nbsp; He overlooked the fact that the power of 10 changed from -3 to +3.&nbsp;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.&nbsp;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.&nbsp;So Excel correctly inserts the decimal point before "312" because "E-03" is like "divide by 1000".&nbsp; And it displays -4.42E+03 (sic; -4,42E+03?) by default because Excel interprets the number as -(4424 + 312/1000).&nbsp;-----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``.&nbsp;Please explain in more detail, step-by-step and perhaps with some images, if only for my edification.&nbsp; TIA.&nbsp;The only interpretation that makes sense to me is:&nbsp; paste -4424,312 (comma decimal point), then format as Scientific.&nbsp; But that presumes that alyna47 has access to the numbers in that form.&nbsp; I doubt that is the case.&nbsp;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.&nbsp;In Excel 2010, I find the option by clicking File &gt; Options &gt; Advanced.&nbsp; Initially, it might look something like this:&nbsp;&nbsp;Click to remove the checkmark, change "Decimal separator" to period and "Thousands separator" to comma, then click OK.&nbsp; It might look something like:&nbsp;&nbsp;After copy-and-pasting, click to restore the checkmark.&nbsp;By default, the pasted values should now appear as -4,42E-03, for example.&nbsp;As SergeiBaklan suggests, change the cell format if you wish to see more precision, -4,424312E-03 for example.&nbsp;

josé maurício gonçalves dos santos · Answer

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.

Forum Discussion

Copy and paste OR import trouble with exponential numbers

9 Replies

Resources