Coffeehouse Thread

4 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

excel displaying a cell in scientific notation

Back to Forum: Coffeehouse
  • User profile image
    SteveRichter

    Doing a lot of reports where the report is a file in CSV format and the user opens the report in Excel.  What can I do to stop Excel from thinking a value is a number that is expressed in scientific notation?

    Open excel. Type the text 1226E1 into a cell and press enter.  The value is displayed in scientific notation ( 1.23E+04 ).  Then you format the cell as text and excel displays 12260, replacing the E1 with a 0.

    I have checked on the excel forum and saw a message saying that is just the way it is, nothing can be done outside of replacing the CSV import file with one in XML open interchange whatever form.

    Enclosing the value in double quotes makes no difference. The best I am doing so far is to append a "." to the front of the value. Which is nuts.

    I actually would not mind having to write the CSV file as XML, but in my brief experience doing that the user could not automatically open an email attachment as XML into Excel like they can a CSV file.

     

     

  • User profile image
    ScanIAm

    If you use OpenOfficeXML to create the file, your users will still be able to open them as email attachments because it will create an actual excel file.  It just won't require excel to create the file so you could do so on a server.

     

     

  • User profile image
    elmer

    @SteveRichter:If I want to force a cell to be text, then I usually apply a ' (single-quote) prefix to it. Excel knows to ignore that character, and treat it as a 'format as text' operator.

  • User profile image
    JoshRoss

    I wouldn't open the CSV from the common dialog. Use the import from text in the ribbon. Ensure that your columns are of type text.

    -Josh

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.