Text turns into date - how to stop?
 

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

Text turns into date - how to stop?
 

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 this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Martha" wrote:

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

Text turns into date - how to stop?
 

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.



Martha wrote:

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

--

Dave Peterson

Text turns into date - how to stop?
 

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

"Shane Devenshire" wrote:

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 this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Martha" wrote:

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

Text turns into date - how to stop?
 

By the way, I tested the vlookup and it worked perfectly!!! Thank you. Martha

"Shane Devenshire" wrote:

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 this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Martha" wrote:

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

Text turns into date - how to stop?
 

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

"Dave Peterson" wrote:

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.



Martha wrote:

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

--

Dave Peterson