why csv fields converted into dates

  • Thread starter Thread starter hilz
  • Start date Start date
H

hilz

Hi
I have a csv file with fields that look like this

,"20-10",

this is being interpreted as a date when i open it in Excel.
what do i need to do with this filed in order to make Excel consider it
a text?
I tried replacing the double quotes with a single quote like this:

,'20-10',

but that makes the single quote appear in the cell in Excel, which is
not what i want.


please help.
thanks
 
hilz said:
I have a csv file with fields that look like this
,"20-10",
this is being interpreted as a date when i open it in Excel.
Click to expand...

Hmm, that's strange, Excel kept it as text when I opened a test file. I
have 2003, what version are you using?
 
I get a date in Excel 2001 with 20/10 or with 20-10

I think Excel 2003 has some options on not treating everything
as a date. Transition options could also come into play.
 
Bucky said:
Hmm, that's strange, Excel kept it as text when I opened a test file. I
have 2003, what version are you using?
Click to expand...


I have 2004 as well.
Maybe your locale is not US, and thus, 20-10 is not really a date!
try with:
"1-1","2-2","3-3"

if i save this line in a csv file and open it, i see

1-Jan 2-Feb 3-Mar


Any suggestions?
 
If you rename the .csv to .txt, you'll see the data import wizard open when you
do File|Open.

You can specify that the input file is delimited by commas. Then specify text
for this field.

(In my simple test, when I specified comma delimited, excel "ate" up those
double quotes. If that doesn't happen for you, you could tell excel that it's
delimited by both comma and other (use a double quote) and then still specify
Text for that field.)

If you need to do this lots, you could record a macro that does this same
thing. But you'll still need to change from .csv to .txt.
 
Maybe your locale is not US, and thus, 20-10 is not really a date!
Click to expand...

You're right, I'm in the US, so 20-10 is not a date. If I try 10-20,
then it did convert to a date. See my other post for the solution.
 
Back
Top