1

The Problem

Microsoft Excel, in its infinite wisdom, chooses to interpret whatever it can as a date when opening a CSV file.

For example, I have exported some data from an external application and one of the fields contains data which is NOT a date, which looks like this:

1/3/1
1/3/2
1/3/3
etc.

When opening the CSV in Excel it displays these as

01/03/2001
01/03/2002
01/03/2003
etc

What I've tried

If I right-click > Format Cells > change to "text" or "general", it's too late - by this time Excel already thinks it's a date and so it changes that to a number like this:

36951
37316
37681
etc.

I'd rather edit the data in Excel (as opposed to a text editor).

Surely someone else has had this problem?

It's 2023, and I thought it's a bit ridiculous posting this without researching first as surely someone else had the same problem, alas here's a list of Stack Overflow issues and why they aren't relevant:

Date Format Changed in CSV File - talks about "saving the date in the desired format". Not applicable.

Keep Excel from converting dates when opening/saving CSV - sounds like the ticket - except that none of the solutions here actually discuss ways of preventing the automatic interpretation of dates by Excel.

Stop Excel from automatically converting certain text values to dates - talks about using a token in the CSV to prevent Excel. Again this is not okay as I need to maintain the CSV's format as it's a template file for importing data into another application.

Retroactively stop Excel from interpreting cell values as dates - talks about doing this retroactively, i.e. she has opened the file in Excel, saved it, then realised it after the fact and wishes to convert back to a specific format.

If someone has better duckduckgo-fu than I, please just link me to the answer.

The Question

Without resorting to changing my Windows regional settings, which apparently alters how dates are interpreted as opposed to actually preventing dates from being interpreted, can someone tell me how to achieve what I want here?

Failing that, I'll gladly change Windows Regional Settings to get this job done, but I somehow feel that I shouldn't have to do this, as I want Excel to allow me to work with the text in the CSV file without any interpretation at the point of opening the file.

hazymat
  • 404
  • 1
  • 6
  • 20
  • 3
    **Import** the file using either the legacy import wizard or power query. At the time of importation you will have the opportunity to designate that column as `Text`. – Ron Rosenfeld Jul 04 '23 at 20:29
  • [This maybe?](https://stackoverflow.com/questions/48767432/date-time-format-in-csv-file-excel) – cybernetic.nomad Jul 04 '23 at 20:30
  • Thanks, both. I think I need a little more hand-holding. There's no "import" option under File. Help tells me to go to File > Open > change file type to text, and open the CSV. This does the same thing. If I start with a blank sheet and go to Data menu > "From Text/CSV" > select the file, it brings up a window with my data in correct format. When I hit "Load" at the bottom, it brings up the data with green headers "Column 1, Column 2" etc and with a filter dropdown for each column. However these "Column 1, Column 2" etc are not in the original file. When saving, this new row shows up :( – hazymat Jul 04 '23 at 20:39
  • p.s. I'm using Excel Professional Plus 2019, for Windows 11 – hazymat Jul 04 '23 at 20:40
  • 2
    You are not the only one. [Scientists rename human genes to stop Microsoft Excel from misreading them as dates](https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates) ;) – SiKing Jul 04 '23 at 20:41
  • 1
    `Data=>From Text/CSV=>**Transform Data**`. Right click on the date column and `Change Type =>Text`. Then `Close and Load`. The Query can be re-used if necessary so you don't have to go through all the steps each time. – Ron Rosenfeld Jul 04 '23 at 20:44
  • Thanks, but this introduces an additional header row, which shows when I re-save the file. Is there a way to prevent that? – hazymat Jul 04 '23 at 20:51
  • Hard to tell since you haven't provide data or code, but if the CSV file is arriving with it's own header row, merely select the option to use the first row as the headers (probably want to do that before the `Changed Type` step) – Ron Rosenfeld Jul 04 '23 at 20:59
  • Thanks. I had selected that option, I was referring to the additional row that is added by Excel during the process (as per above comment), not relating to my existing header row. I used legacy import wizard, which resolves the problem. Cheers. – hazymat Jul 04 '23 at 21:33

1 Answers1

1

Here's a text file that I'll use:

1/3/1, Hello
1/5/2, Bye

I have M365 Excel on Windows.

Let's go to Data tab -> From Text/CSV

enter image description here

Excel asks me to choose my CSV file. After I chose it, Excel shows me what it believes it'll import. As you noticed, 1/3/1 is converted to a date - which is what you don't want.

enter image description here

At the bottom of that wizard is a button called Transform.

enter image description here

When I click on it, the results are shown in Power Query.

enter image description here

I'll remove the step called Changed Type that Excel wisely introduced.

enter image description here

Data is then correctly interpreted as 1/3/1.

Click on Close and Load.

enter image description here

And there we have the data in Excel in the format we wanted.

enter image description here

Using Legacy Wizard

If you don't have the legacy wizard, use https://officesmart.wordpress.com/2018/06/18/turn-on-the-legacy-wizards-in-microsoft-excel-365/ to turn legacy wizard ON.

Go to Data > Get Data > Legacy Wizards > From Text

enter image description here

Choose your file and keep the default option of Delimited.

enter image description here

Choose the Comma delimiter.

enter image description here

Click on the column of your choice and make sure it is marked as Text.

enter image description here

You can change other column's data types as well by clicking on them and changing the type.

When you load the data, you will see no column headings. Just the data like so:

enter image description here

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • Thanks. Maybe you wrote this just before I updated the comments on my original post, but my beef with this was that it introduces a new row to the file called "Column 1, Column 2", etc, which when you then subsequently save that sheet back to the CSV file, it includes that new header row (even though, in my case, there's already a header row). I tried to remove that row in Excel but there was no way to do that. – hazymat Jul 04 '23 at 20:48
  • In my latest edit, using legacy wizard, column1/2/3 are not added and therefore won't be saved to a CSV when you save the file as a CSV. – zedfoxus Jul 04 '23 at 20:52
  • 1
    Thanks, I didn't know how to get the legacy wizard which is something I recognise from previous versions, but your post showed me how to do that. – hazymat Jul 04 '23 at 20:57
  • 1
    @hazymat if you were to use Power Query, which creates a fancy table, you can use this answer to remove formatting Power Query added and remove the heading it inserted. https://superuser.com/a/1574857 – zedfoxus Jul 04 '23 at 21:01