Search code examples
excelcsvotrs

How to prevent Excel from changing long numbers to E format and rounding to zero

When I export search result to CSV file from OTRS and open the CSV file in Excel, I get the ticket number as: 2,01101E+15 .

No problem until now, but when I change the number to text field the last digit of ticket number is rounded to zero. e.g if I have a ticket number 123456789 then excel changes it to 123456780.

How can I resolve this problem?

Solution

  • there is actually only one way in Excel to do this. Create an empty sheet -> Choose File->Import->CSV and select your CSV file. The delimiter should be a semicolon and fields are enclosed by ". In the next step you have to choose "Text" for the Ticket# column.

    This is a very annoying Excel "bug". Another way to do this would be a macro, but running macros is often not allowed.