Editing CSV file in Excel always converts number column to scientific notation

Question

I have a CSV file that I'm editing in Excel 2013. In the file there is a column that contains numbers e.g 688087123456789. Everytime I open the CSV file in Excel, it converts the number to scientific notation 6.88087E+14. I can revert the number back to its original format by right-clicking the cell > Format Cells > Custom and then selecting 0.

However, I do not want to do this process everytime I open teh CSV file. How can I do this?

Solution

It is still not possible, as already answered here. You may only use an alternative approach described here:

Stop double-clicking the .TXT/.CSV file or opening the text file conventionally (e.g. CTRL+O) and use the Text Import Wizard to specify Text format for certain fields within your import file. Start by opening Excel to a blank workbook and from the Data tab's Get External Data group, click the From Text button.

After clicking From Text, select your text file from the Import Text File dialog and click Open. This launches the Text Import Wizard. There are three pages to complete. The first page is essentially to determine whether the file is to be considered Delimited or Fixed Width. The second page depends upon the first. You will be asked what characters to consider as delimiters or asked to choose the widths of a fixed width file.

The third page is what is going to help you determine the format of each field. Select your account id field from the Data preview at the bottom then select Text from the Column data format list in the upper left. Note that the header of the field in the bottom Data preview has changed to Text. Repeat for any other fields and click Finish in the lower right to complete your operation.

Another way is to use VBA script, like the one here.

Comments

[Vojtěch Dohnal]

superuser.com/questions/452832/…

[Justin Goldberg]

That does not help. This continues to occur with CSV files that are opened, rather than pasted. The solution is to open the file in notepad and paste into Excel or use the data import wizard.