0

I have several very large .csv files that I need to work with, and Excel makes this very easy (obviously). One of the fields is in the format 10/3/2013 19:00, and Excel automatically converts this to its own date/time format when it opens the .csv file. I need to handle the raw text data, however. If I select the column and change the cell format to Text, Excel changes the values to just decimal numbers (41550.7916666667 in the above example).

How can I get Excel to just display all of the fields in text format? I'm using Excel 2013.

  • I have no excel at home to test with, but you could try wrapping the date fields in quotes. E.g. "10/12/2013 19:00". Ofc. that means editing the .cvs files before opening them in excel, which is a hassle. – Hennes Oct 28 '14 at 22:01
  • If you actually want to use it as a date, let Excel store it as a date (the number), and set the display format to the date format you want. – fixer1234 Oct 28 '14 at 22:09
1

Use Data, Get External Data, From Text to import your CSV. That will retain the field types better than opening directly in Excel. Excel has to "guess" what the field type is and often gets it wrong. When you use Data, From Text, this is more explicit. You can also refresh your data as long as you keep the CSV file in the same location.

enter image description here

0

When you open the file, use the Import Wizard. You can the the Wizard which fields you want imported as Text.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.