Text turns into date - how to stop?

  • Thread starter Thread starter Martha
  • Start date Start date
M

Martha

I have a file that associates codes that look like dates (OCT26 for example)
with a Saints Day. When I export to excel via .csv, it looks like 26-Oct, and
if I then pull the cell over into another worksheet, I get the number date
code 40112. The column is defined as TEXT. I need to compare the codes to
the table via VLOOKUP, so I can disply the description in the report. Is
there anyway to tell Excel not to try to make this code a date? (I did try
the ' suggestion, but it doesn't work in this case).
Martha
 
Hi,

When you come throught the import wizard, you can select the column of
concern and click the Text option.

If that doesn't work you can use a formula against the 41002 cell

=TEXT(C1,"MMMDD")

In fact you can write a formula such as

=VLOOKUP(TEXT(C1,"MMMDD"),G1:H100,2,FALSE)
 
If you import the .csv file using Data|Import external data (xl2003 menus),
you'll be able to specify that this field should be treated as text -- and excel
won't change it.

You could also rename the .csv file to .txt and use File|Open. You'll see that
familiar text to columns wizard where you can specify that the field should be
treated as text.
 
Shane - thank you so much! I'll try it. But one question, how do I make excel
open the file via the import wizard. It just opens to .csv immediately. Is
there a preference or something I can check? I have 2007.
Martha
 
I had thought of opening excel first and then loading it in, but after I sent
email - but I like the idea of changing the name to .txt best. I do know how
to change the format of fields in the wizard. Thank you. Martha
 
Back
Top