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.
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.