0

I have a .csv file looks like this:

"13,423.354679",

When I open it in Excel it looks like this:

13,423.35

I can let it show correctly by changing the format manually.

Is there a way I can keep it shown correctly without formatting in Excel?

pnuts
  • 58,317
  • 11
  • 87
  • 139
maoyang
  • 1,067
  • 1
  • 11
  • 11
  • Sorry, could you please explain with more detail? Thx – maoyang Nov 18 '15 at 04:18
  • Another way of formatting can be done in "Change Calender Settings" ->"Customize Format"->Numbers->Change Decimal to 9. then logoff and login. – Sid Nov 18 '15 at 05:02

3 Answers3

0

Unfortunately, not really. Excel by default displays 2 decimal places, according to here :

By default, Excel displays 2 decimal places when you apply a built-in number format, such as a currency format or a percentage, to the cells or data. However, you can change the number of decimal places that you want to use when you apply a number format

Caffeinated
  • 11,982
  • 40
  • 122
  • 216
  • Thanks for reply. I found that "13423.354679" can be shown as 13423.354679. But "13,423.354679" not, only 2 decimal kept, shown as 13,423.35. Because there is a comma in the number. – maoyang Nov 18 '15 at 03:43
0

You can show as many as you want, including none.

Click for Format Picture

AND use the 1000 separator option to split the numbers like you want = 13,423.35.

And yes you can't normally have the actual commas, the formating option just puts them there for visual appearance - but if you really want them someone can write you a vba in 1 minute. Keep in mind excel can't work with the numbers normally if they have actual commas in them. You have to duplicate the data in case you want to both work with the numbers and have an output that has them. In case one you can remove them with =SUBSTITUTE() and have VBA or a clever formula put them back in for your final use of the data.

In case of image loss: Home > Number Format (where it says General) > More Number Formats > Category Number > Decimal Places controller.

If for some reason they get lost on import - just open the csv with something else and copy-paste the numbers into excel.

helena4
  • 282
  • 1
  • 3
  • 13
  • Hi, helena4, it is a nice solution. But I want to know how to control it in .csv file. Our user don't want to do it manually. But I think we can't control it in .csv file. – maoyang Nov 19 '15 at 00:22
  • 1
    You can't control it in a csv file. Not without much extra customization or a kickass editor. What you can do is open the csv in excel, insert/remove whatever you want from it and export save/export back to csv. I have sought out vba for similar purposes. So i always work with a xlsm and just 1 click it to csv anytime I need. http://stackoverflow.com/questions/26402010/a-working-vba-that-exports-excel-to-csv-utf8-get-it-now Frankly I don't know why would people want to WORK on the csv directly anyway. Opening it in a good editor - seems always preferable for the gained functionality. – helena4 Nov 19 '15 at 09:09
0

I don't think we can control the format in .csv files.

slk can do limited format control for Excel. Cons is it only support ANSI and not very common use.

I also try to delete the comma from the number, then it becomes a general number which can show 11 digits.

maoyang
  • 1,067
  • 1
  • 11
  • 11