Preventing Excel from interpreting values as dates

July 4, 2023

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:

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.

>Solution :

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

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.