Please help me about .csv file

Question

Hello all the expert,

I have a simple question, simple for you but it trouble me for whole day to get a solution. Let me explain this:

I have a .csv file and I open it with Excel 2013. It looks something like this:

I do not want to see the 1.00002E+14 or 1.00001E+14 as I know it is a number. What I did was I right click it at the column A and choose "Format Cells", under the "Numbers" tab I choose "Special". It then change everything to number. Example: 1.00001E+14 become 100001957209850

After that I press CTRL+S to save it.

I proceed to click the X button to close the Excel file and now it ask me a question "Want to save your changes to blablabla.csv?", I click "Yes".

Now here is the problem:

I reopen that file again and all the data become like this, thy are in a single row, without column:

• My question is simple, how can I set the "Right Click>Format Cells>Numbers>Specific" to default? So that whenever I got a new .csv file the number doesn't look like an alien, or 1.00002E+14 and I do not have to manually change the Format Cells to "Specific".
• If the first one cannot be done, then how can I save a .csv file? (after changing the format cell to "specific" and all the data remain at the same row same column?

Please help and thanks in advance!!!

Regards,

Kivor

Answer 1

Hi,

Thanks all the replied.

It sound like it cannot be done. :(

I had open a new file, copy and paste the data in it and save it as XLS. Everything looks fine even I reopen it, the number remain 49348383939138448 (example).

And then I save it again in .CSV and the number become 1.00002E+14 This is so weird.

Answer 2

Hi Kivor,

Excel interpret the values and gives it the best fitting format. It does not mean that the values in the cell have changed. Try it with changing the format to number, no decimals.

Answer 3

CSV does not save the formats of Excel, therefore the Excel formatting lost everytime you reopen a CSV.

I suggest you to save two versions, 1 XLS and 1 CSV

Answer 4

Hi Kivor,

See the suggestions of joeu2004 in the final post in the following thread:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/convert-scientific-notation-to-text-and-saving-in/5920344e-cc48-e011-8dfc-68b599b31bf5

===

Regards,

Norman