Excel CSV - Number cell format

Question

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

User · Answer

Adding a non-breaking space in the cell could help. For instance: "firstvalue";"secondvalue";"005 ";"othervalue"

It forces Excel to treat it as a text and the space is not visible. On Windows you can add a non-breaking space by tiping alt+0160. See here for more info: http://en.wikipedia.org/wiki/Non-breaking_space

Tried on Excel 2010. Hope this can help people who still search a quite proper solution for this problem.

User · Answer

Well  excel never pops up the wizard for CSV files  If you rename it to  txt  you ll see the wizard when you do a File Open in Excel the next time

User · Answer

I believe when you import the file you can select the Column Type   Make it Text instead of Number   I don t have a copy in front of me at the moment to check though

User · Answer

When opening a CSV  you get the text import wizard   At the last step of the wizard  you should be able to import the specific column as text  thereby retaining the  00  prefix   After that you can then format the cell any way that you want   I tried with with Excel 2007 and it appeared to work

User · Answer

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

User · Answer

This works for Microsoft Office 2010  Excel Version 14  I misread the OP s preference  to do something to the file itself   I m still keeping this for those who want a solution to format the import directly   Open a blank  new  file  File -  New from workbook  Open the Import Wizard  Data -  From Text  Select your  csv file and Import In the dialogue box  choose  Delimited   and click Next  Choose your delimiters  uncheck everything but  comma    choose your Text qualifiers  likely  None    click Next In the Data preview field select the column you want to be text   It should highlight  In the Column data format field  select  Text   Click finished

User · Answer

The Text Import Wizard method does NOT work when the CSV file being imported has line breaks within a cell  This method handles this scenario at least with tab delimited data     Create new Excel file Ctrl A to select all cells In Number Format combobox  select Text Open tab delimited file in text editor Select all  copy and paste into Excel

User · Answer

Load csv into oleDB and force all inferred datatypes to string  i asked the same question and then answerd it with code   basically when the csv file is loaded the oledb driver makes assumptions  you can tell it what assumptions to make   My code forces all datatypes to string though     its very easy to change the schema  for my purposes i used an xslt to get ti the way i wanted - but i am parsing a wide variety of files

User · Answer

This has been driving me crazy all day  since indeed you can t control the Excel column types before opening the CSV file   and this worked for me  using VB NET and Excel Interop            Convert  csv file to  txt file          FileName   ConvertToText FileName           Dim ColumnTypes    As Integer   New Integer      1  xlTextFormat                                                              2  xlTextFormat                                                              3  xlGeneralFormat                                                              4  xlGeneralFormat                                                              5  xlGeneralFormat                                                              6  xlGeneralFormat             We are using OpenText   in order to specify the column types          mxlApp Workbooks OpenText FileName      Excel XlTextParsingType xlDelimited      True    True        ColumnTypes          mxlWorkBook   mxlApp ActiveWorkbook         mxlWorkSheet   CType mxlApp ActiveSheet  Excel Worksheet    Private Function ConvertToText ByVal FileName As String  As String      Convert the  csv file to a  txt file       If the file is a text file  we can specify the column types       Otherwise  the Codes are first converted to numbers  which loses trailing zeros       Try         Dim MyReader As New StreamReader FileName          Dim NewFileName As String   FileName Replace   CSV     TXT           Dim MyWriter As New StreamWriter NewFileName  False          Dim strLine As String          Do While Not MyReader EndOfStream             strLine   MyReader ReadLine             MyWriter WriteLine strLine          Loop          MyReader Close           MyReader Dispose           MyWriter Close           MyWriter Dispose            Return NewFileName     Catch ex As Exception         MsgBox ex Message          Return        End Try  End Function

User · Answer

You can simply format your range as Text   Also here is a nice article on the number formats and how you can program them

User · Answer

Don t use CSV  use SYLK  http   en wikipedia org wiki SYmbolic LinK  SYLK   It gives much more control over formatting  and Excel won t try to guess the type of a field by examining the contents   It looks a bit complicated  but you can get away with using a very small subset

User · Answer

Actually I discovered that  at least starting with Office 2003  you can save an Excel spreadsheet as an XML file  Thus  I can produce an XML file and when I double-click on it  it ll be opened in Excel  It provides the same level of control as SYLK  but XML syntax is more intuitive

User · Answer

Put a single quote before the field  Excel will treat it as text  even if it looks like a number        005       EDIT  This is wrong  The apostrophe trick only works when entering data directly into Excel  When you use it in a CSV file  the apostrophe appears in the field  which you don t want   http   support microsoft com kb 214233

User · Answer

I had this issue when exporting CSV data from C  code  and resolved this by prepending the leading zero data with the tab character  t  so the data was interpreted as text rather than numeric in Excel  yet unlike prepending other characters  it wouldn t be seen     I did like the   001  approach  but this wouldn t allow exported CSV data to be re-imported again to my C  application without removing all this formatting from the import CSV file  instead I ll just trim the import data

User · Answer

Just add   before the number in the CSV doc

User · Answer

I know this is an old question  but I have a solution that isn t listed here   When you produce the csv add a space after the comma but before your value e g    005      This worked to prevent auto date formatting in excel 2007 anyway

[excel] Excel CSV - Number cell format

Examples related to excel

Examples related to csv

Examples related to formatting

Examples related to number-formatting