Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Stop Excel from changing large "numbers" (actually text values) to scientific notation.

Prevent Excel from changing large "numbers" (actually text values) to scientific notation.

1,580 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

Caleb shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

489 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • anonyzak commented  ·   ·  Flag as inappropriate

    @Futski:
    don't talk to MS about doing things right. they have everyone believing a megabyte is 1024 kilobytes...

  • Futski commented  ·   ·  Flag as inappropriate

    @Roy,
    Why does that not surprise me!!! If MS wants to support CSV, they really ought to do it right. Thanks for trying.

    Hey MS,
    Double Quotes in - Format as Text
    Formatted as Text - Double Quotes out
    Should be simple. Lets get it done.

  • Roy commented  ·   ·  Flag as inappropriate

    @Futski:

    Just tried that, out of some interest, eh? Edited the CSV file, saved it with NotePad, opened in Excel and it completely ignored them.

  • Futski commented  ·   ·  Flag as inappropriate

    If only Excel would save Text Formatted fields with double quotes around them in the CSV file (a csv standard), then the problem would be solved.
    If only... sigh...

  • Jon commented  ·   ·  Flag as inappropriate

    For this issue, A. Nonny Mouse, I maintain an .xlsx/.xlsb file for my main file with edits. And I use either formatting of the cell (number, with 0 digits) or Text to Columns to force all the digits to show in the cells in the Excel formatted file. (Since our issue is with ISBN's, we can handle text.) So, I edit the excel file. Save it.

    Then... I export as a .csv file. Then the data is stored properly in the .csv.. This sucks as you always have to go back to your Excel to make the edits, then save it again as a .csv.

    The trick is that the export to CSV saves what is _visually_ displayed when it exports. Regardless of field type.

    When you open the CSV generated from an Excel file, a long saved number will _look_ like scientific notation again. But, if it was saved as the full number, that full number will still be there, behind the format.

    And so you just have to click a cell to prove it to yourself that the full number is there. And you can reformat the column also to prove it.

    If you have something using those cells for calculation, it won't matter about the visual format on _opening_ the CSV as long as the value is stored in the CSV itself.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Thanks so much to Ed and Jon for their responses. Unfortunately I am not an excel whiz, so am not familiar with Access. It must be in a csv file for the secondary program to run. I tried saving in excel, then converting back to csv, but the csv file switched back to the scientific formula.

    I will check into Access and see if I can work with that.

    Thanks again and I really appreciate your efforts

  • Jon commented  ·   ·  Flag as inappropriate

    Ed's correct. Excel always sees a .csv as a raw file and reinterprets it again all over again. .xlsx or .xlsb is the way to go. You can use "Text to Columns" to force the column to a text value, so that it will display properly in pivots too.

    Another option, if it must be in a .csv file, is to use Access and then Excel. The method is to do an linked file to the .csv. Define the field as text in Access. Then use a linked table to the external data source in Excel out to the Access table. It's a bit crazy, but it should work.

    Also, please fix this Microsoft. It can just be a simple setting. Or even a registry setting. We are a publisher, and our entire company is affected because of ISBN's.

  • Ed Hansberry commented  ·   ·  Flag as inappropriate

    Sounds like you aren't saving it as an *.XLSX file. If you save it back as a CSV, formatting is not saved, only data. I've never seen that happen (lose its format preferences) with a true Excel file.

  • Anonymous commented  ·   ·  Flag as inappropriate

    This works as long as I am in the file, but once I save, exit and reopen the file, it converts back to the scientific formula. Any ideas on how to help with that? Thanks!!

  • Pat Babcock commented  ·   ·  Flag as inappropriate

    @Ccis Bmkh: Yes,yes - we all saw your brilliant YouTube the first time you posted it earlier the same day. We're all thoroughly amazed by your brilliance, and how you stumbled upon a concept absolutely none of us could possibly have conceived of- particularly since we crave a behavior that allows numbers to remain numbers. You can stop now. Thanks.

  • Pat Babcock commented  ·   ·  Flag as inappropriate

    @Anonyzak spake thusly: "i'm sure there are some statistician/scientists who DO use Scientific Notation"

    <hand raised>

    Excel is the "Engineer's word processor". Statistically, there are huge numbers of engineers using Excel, and we are pretty much all adherents to Scientific Notation by nature of the data we play with. HOWEVER, I'm also one of the early respondents to this thread: Excel should not convert numbers on import - especially since, in converting to SciNot, they drop significant digits. The number import (and entry) behavior should be user-selectable, both globally and in the import dialog... And as one of those respondents back ... What? Four years ago, now? ... I am equally frustrated that this seems to have been going absolutely nowhere at a very high rate of speed. It's almost as if UserVoice is for input to politicians...

  • anonyzak commented  ·   ·  Flag as inappropriate

    @Anonymous: i'm sure there are some statistician/scientists who DO use Scientific Notation, however, statistically speaking, think of the number of them compared to the rest of the world.
    it is probably right on par with the @ProfessorMickelson estimate.

  • Anonymous commented  ·   ·  Flag as inappropriate

    I wonder - if Excel did away with scientific notation completely, how many people would notice?

    I suspect that Excel has this "feature" to convert large strings to scientific notation, that hardly anyone at all would want to use.

  • Anonymous commented  ·   ·  Flag as inappropriate

    @Ccis Bmkh yes we know. You can also put a single quote ' in front of the digits to make Excel treat it as text format. The issue is that we should not have to do anything in Excel to stop the scientific notation since it is far less often that scientific notation is wanted.
    Anonymous @ProfessorMickelson estimate is probably correct. :)

  • Leigh McDonald commented  ·   ·  Flag as inappropriate

    An excellent question. This "feature" is a major problem for a huge portion of your user base (speaking as a publisher, I can tell you that dealing with metadata in Excel is required by the industry and 13 digit ISBNs are a total nightmare because of this one issue.)

    I'm having a hard time imagining why ANYONE would want a field that claims "The cell is displayed exactly as entered" to *actually* convert numbers to a completely different and very specific format that is useless for most applications?

← Previous 1 3 4 5 24 25

Feedback and Knowledge Base