Share via

Bug in MS Excel with text that mimics scientific notation

Anonymous
2021-07-27T22:08:52+00:00

My client has data that they review using MS Excel. For two records, the property block/lots were forcibly turned into scientific notation. I tested the issue myself, and saw the two bad fields. So I rendered the query in SSMS, set the destination columns in MS Excel 365 to TEXT, and copied the data into the text-formatted columns. It still forced those two fields into scientific notation. If I simply changed the fields to text, it didn't change the data back to its original format - it left them in scientific notation. I also tried to cast them on the SQL side as specifically text fields (though they already were text), but that didn't help either.

The two fields are:

5428E-028 was changed to 5.43E-25

0436E-022 was changed to 4.36E-20

These are automated reports that the users view in a reporting tool and download automatically into MS Excel. This seems like a bug to me. I had to manually fix and send them the report, which isn't a great solution.

Is there any way to catch this problem before it reaches the client?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2021-07-27T23:47:32+00:00

If the data is coming from a CSV file (and assuming it is correct in that CSV file), perhaps teach them (or provide a query or macro) that imports the file. Using either Power Query or the Legacy import wizard, they (or you if you are providing the macro/query) can designate that column as text before Excel munges the data.

Was this answer helpful?

1 person found this answer helpful.
0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-07-28T01:22:47+00:00

    Hi AmyLangston1!

    I'm Jen, an independent advisor and a Microsoft user like you. I'd be happy to help you out with this issue.

    Try formatting the cells first as text before you type or paste the values into the cells. Let me know if this works.

    I hope this information helps. If you have any questions, please let me know and I'd be glad to assist you further.

    Best Regards!

    Jen :)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-07-27T23:45:59+00:00

    Seems I'll need to revisit this later.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-07-27T22:51:17+00:00

    Hi Shane,

    The problem is, that doesn't solve the problem. It's the first thing I tried, because it's the sensible approach! It will render the two odd values as text, but it won't change them back to their original value. So 5.43E-25 turns into a text version of 5.43E-25, but it doesn't revert back to 5428E-028. That original value is completely lost.

    I've done some experimenting. If I download the data directly from the report and into Excel, it displays just fine. I think what they are doing is receiving a CSV (data is accurate at that point), and then opening it in Excel to take a look. Once Excel gets its dirty mitts on the data from CSV format, it renders those two values as scientific notation, and there is absolutely nothing you can do at that point. I personally think this is a bug - taking a text field from an entire column of text fields and recasting it as scientific notation is not rational. But perhaps we can do some user retraining... "It processes the records from CSV. It doesn't look at the data in Excel."

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-07-27T22:32:35+00:00

    The order of execution is critical. The most straight forward approach is to 1. copy the data into Excel. 2. Format the cell to text, 3. Press F2, Enter. This method is fine if you are only doing a few cells. But for a lot of cells:

    1. Copy the data into Excel, 2. Select the data and choose Data, Text to Columns, Next, Next, select Text and click Finish:

    Was this answer helpful?

    0 comments