Excel – Numbers in scientific notation imported to Excel 2016 as text

microsoft excelmicrosoft-excel-2016

This is a repeat of Error in importing the numbers in scientific notation. That question was never answered and I am not allowed to comment. I am having the exact same problem with Excel 2016.

In .txt file:

ch/det n           a              b           g
01/1   679.38102   -0.015162251   1.0000871   -8.8448806e-08

I import this as fixed width and force the first column to import as text. The 6th column (-8.8448806e-08) imports to column E as General, but when I try F2 = E2+5 I get the #VALUE error. If I highlight column E and set the type drop-down to "Scientific", I continue to get the #VALUE error for this math. When I click on E2 and look at the cell contents, there is no leading apostrophe.

I have check my region settings on Windows 10 and I can't find any that should affect scientific notation. I've checked the advance options in the Excel import wizard and there are no options that apply to scientific notation.

Unlike every other question I have managed to find, I WANT these numbers to be scientific notation and I want to be able to use them in math formulas.

Edit:

My stop-gap solution has been to manually remove all the weird column spaces and replace them with a single tab to create a tab delimited .txt file. The original file only had 74 lines, so that was possible. Now it's importing ok, but if that text file were any larger, this solution would be unreasonable, so I would still like this question answered.

Another thing I have tried:

In a free cell, I do =Left(E2,LEN(E2)-4). This removes the e-08 and leave a more regular number. Just in case, I set the data type of this new cell to number and attempt to use it in math. Nope, still #VALUE. I tried Hannu's trick of multiplying by 1, but I still get #VALUE.

Best Answer

Ok. I replicated your problem by adding two spaces and a tab character in front of the last number in the text file. It did what you described.

To solve the problem, I then opened up the file as a delimited file, with space and tab delimiters checked, and checked the box that states "treat consecutive delimiters as one". The last number was now imported as scientific notation and I was able to do calculations from it.

Related Question