Excel CSV - 数字单元格格式 [英] Excel CSV - Number cell format

问题描述

我将报表生成为CSV文件。
当我尝试在Excel中打开文件时，它根据单元格的内容做出关于数据类型的假设，并相应地重新格式化。

例如，如果CSV文件包含

  ...，005，... 
  
 
 
 
然后Excel显示为5. 
是否有办法覆盖此显示并显示005？
 
 
 
我喜欢对文件本身做一些事情，所以用户只需双击CSV文件即可打开它。
 
 
 
我使用Excel 2003。
解决方案
打开.csv文件时没有一种简单的方法来控制Excel应用的格式。下面列出了可能有帮助的三种方法。
 
 
 
我的首选项是第一个选项。
 
 
 
 选项1  - 更改文件中的数据 
 
 
 
您可以按如下方式更改.csv文件中的数据...， =005 ，... 
这将在Excel中显示为...， 005 ，... 
 
 
 
 Excel会将数据保存为公式，但复制列并使用粘贴特殊值将会删除公式，但保留格式
 
 
 
 选项2   - 格式化数据
 
 
 
如果只是一个格式问题，并且该列中的所有数据都有三位数字。然后在Excel中打开数据，然后使用此自定义格式 000  
 
 
 
 选项3  - 将文件扩展名更改为.dif（数据交换格式）
 
 
 
更改文件扩展名，并使用文件导入向导控制格式。 
。双击扩展名为.dif的文件时，会自动通过Excel打开。
 
 
 
 一步一步： 
 
 
 
将文件扩展名从 .csv 更改为 .dif  
 
 
双击文件以在Excel中打开它。
 
 
将启动文件导入向导。
 
 
 
 
 
在分隔符下，选择逗号，然后单击下一步按钮。
 
 
点击显示的数据的每一列，然后选择列数据格式。具有值005的列的格式应为文本。
 
 
单击完成按钮，文件将由Excel以您指定的格式打开。 li> 
 
 
I produce a report as an CSV file.
When I try to open the file in Excel, it makes an assumption about the data type based on the contents of the cell, and reformats it accordingly.

For example, if the CSV file contains
...,005,...
Then Excel shows it as 5.
Is there a way to override this and display 005?

I would prefer to do something to the file itself, so that the user could just double-click on the CSV file to open it.

I use Excel 2003.
解决方案
There isn’t an easy way to control the formatting Excel applies when opening a .csv file.  However listed below are three approaches that might help.

My preference is the first option.

Option 1 – Change the data in the file

You could change the data in the .csv file as follows ...,="005",...
This will be displayed in Excel as ...,005,...

Excel will have kept the data as a formula, but copying the column and using paste special values will get rid of the formula but retain the formatting

Option 2 – Format the data

If it is simply a format issue and all your data in that column has a three digits length.  Then open the data in Excel and then format the column containing the data with this custom format 000

Option 3 – Change the file extension to .dif (Data interchange format)

Change the file extension and use the file import wizard to control the formats.
Files with a .dif extension are automatically opened by Excel when double clicked on.

Step by step:


Change the file extension from .csv to .dif
Double click on the file to open it in Excel.
The 'File Import Wizard' will be launched.
Set the 'File type' to 'Delimited' and click on the 'Next' button.
Under Delimiters, tick 'Comma' and click on the 'Next' button.
Click on each column of your data that is displayed and select a 'Column data format'.  The column with the value '005' should be formatted as 'Text'.
Click on the finish button, the file will be opened by Excel with the formats that you have specified.


                        
这篇关于Excel CSV  - 数字单元格格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！