Copy and paste OR import trouble with exponential numbers

[José Maurício Gonçalves dos Santos]

Hello,

     When I copy an number or import a TXT file with an negative exponential number (bellow) to excel

3.03425675881e-05

the number is altered to a positive exponential and even the exponential value was altered (bellow).

3,03E+06

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.

[#2]

Hi Jose,

I was not able to reproduce it. I copy your number and paste to Excel; when create txt file with it and import - have correct number in both cases

Could you please attach your txt file with few numbers to check?

[#3]

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.

[#4]

So far so good, thank you for the update

[#5]

@Sergei Baklan 

I am facing the same problem, when I paste 

-4.424312e-003 in excel it gets translated to -4.42E3

[#6]

@alyna47 

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

, value will be the same.

[#7]

@alyna47 

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.

[#8]

Yes:  This is __not__ a formatting problem, as @Sergei Baklan 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 @Sergei Baklan suggests, change the cell format if you wish to see more precision, -4,424312E-03 for example.

[#9]

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.

[#10]

@José Maurício Gonçalves dos Santos 

If you want to import a batch of numbers without changing your system settings you could bring them into a text-formatted range.  The use SUBSTITUTE to remove any English thousands separators "," (if any exist) and replace the decimal point "." by the character used by your machine locale (presumably ",").

Coerce the formula to a number by preceding it with --.

= --SUBSTITUTE(
      SUBSTITUTE(numbers, ",", ""),
    ".", ",")

Not very nice at all, but better than retyping numbers!