Why does Excel mis-convert a string to a date

[Nickred001]

In a CSV file I have <<,"6 - 14",>>. On opening the file Excel 2007
strangely converts this to 14 Jun as a number. Clearly the CSV file said
this was a string. Why does Excel think it knows better and give a poor user
outcome.

Regards

Nick

 

[Gord Dibben]

Can you rename the *.csv to *.txt before opening?

The text wizard will then pop up allowing you to choose in third step what
format each column is to be.

Select Text for that column.


Gord Dibben MS Excel MVP

 

[Nickred001]

Gord

Unfortunately my data is not a simple database format. It has some header
rows at the to an when I try your trick id does not work. Excel still
persist as reading 6 - 14 as 14 June.

Regards

Nick

 

[Dave Peterson]

How did it not work?

If you rename the file to *.txt, then when you use File|Open, you should see a
wizard that allows you to specify that it's delimited (by commas) and in another
step, you can specify that any (or all????) field can be treated as text.

If you have data in that same field that should be treated as dates, you could
import them all as text and then convert the real dates to dates--using whatever
rules that you need.