2

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.

  • Is the imported text file comma separated?? – Gary's Student Dec 15 '17 at 16:35
  • No, it has spaces. The header row has 14 characters per column (eg, ch/det followed by 8 spaces, then n followed by 13 spaces, etc.) the following rows have columns sized: 11 chars, 13 char, 19 chars, 13 char and 14 char. The spots not taken up by data are spaces (eg, 01/1 followed by 7 spaces, 679.38102 followed by 4 spaces, etc). For some reason, the space between the 5th and sixth column is two spaces and a tab, instead of 4 spaces. No idea why. – SatelliteEyes Dec 15 '17 at 16:42
  • Have a look at superuser.com/a/1137829/346288 – Hannu Dec 15 '17 at 17:22
  • This solution did not work for me. At the end of the steps, I still get #VALUE when trying to use those numbers. Setting the data type to general or number appears to have no effect. All I can think of is that the leading zero on the exponent (eg. e-08) may be preventing Excel from parsing accurately. – SatelliteEyes Dec 15 '17 at 18:00
  • (Edit: I see you've tried the opposite and that works) Have you tried replacing all the tabs between 5th and 6th columns with a space? It imports as a number without tabs for me but as soon as I add the tab it breaks as you've described (6th column imported as text). Would it not be easier to replace all tabs with a space using a suitable tool (e.g. text editor or command-line utility)? – wardies Dec 15 '17 at 18:32
0

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.

1

When importing, rather than choosing "Fixed width" as the data type, try "Delimited".

Then on the next page, tick the following boxes:

  • Tab
  • Space
  • Other: /

You should be good to go.

It seems that the fixed width importer gets confused if there is a mixture of spaces and tabs, as you discovered.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.