Turning off Automcatic Date Format

[Tay]

Hi Everyone,

I am importing information from a text file to excel and creating columns for each field of data. My problem is that some of the numbers are being converted into a dates because there are hyphens inbetween the numbers. When I format the cell and put it back to a number or general format, it removes the hypens. This is a problem because I need to do vlookups on the data and the hyphens need to be in there. The most simple solution I can think of is to turn off the date auto format before I import the file to excel but I can't find the option anywhere. Is it even possible to turn it off?

Thanks in advance.

[Maistrye]

Hi Everyone,

I am importing information from a text file to excel and creating columns for each field of data. My problem is that some of the numbers are being converted into a dates because there are hyphens inbetween the numbers. When I format the cell and put it back to a number or general format, it removes the hypens. This is a problem because I need to do vlookups on the data and the hyphens need to be in there. The most simple solution I can think of is to turn off the date auto format before I import the file to excel but I can't find the option anywhere. Is it even possible to turn it off?

Thanks in advance.

Are you importing a .csv file? If so, the easiest way to make sure it formats the way you want is to change the extension to .txt. This way, it walks you through the whole import process allowing you to choose what separator is used on the data. More importantly for you, it allows you to specify on the last screen the Data Type for each field before it gets imported.

Scott

[Tay]

No, it's just a plan txt file. On the page you're refering to; I have already tried to specify that the entire column should be numbers or general but it still converts it into a date.

Edit: I'm mistaken, only 4 options exist for the data type and numbers isn't one of them. Just general, text, date and skip column.

However, I tried using text and it remained in number format. I then formatted the column to numbers and it didn't change to the date format; so it would seem my problem is solved. However, if there is still a way that anyone knows of to actually turn off this auto date format I would still like to hear it. I have no use for this function and it has caused some problems for me in the past.

Thanks again.

[ibexcel]

why don't you change the cell format to 'text'

[Bryan Hessey]

No, it's just a plan txt file. On the page you're refering to; I have already tried to specify that the entire column should be numbers or general but it still converts it into a date.

Edit: I'm mistaken, only 4 options exist for the data type and numbers isn't one of them. Just general, text, date and skip column.

However, I tried using text and it remained in number format. I then formatted the column to numbers and it didn't change to the date format; so it would seem my problem is solved. However, if there is still a way that anyone knows of to actually turn off this auto date format I would still like to hear it. I have no use for this function and it has caused some problems for me in the past.

Thanks again.

Hi,

The date was previously a problem in Excel '97, I believe there was no option to select, but that the date was recognised by the format of the first date in the column, alas all to often as the American format.

Defining the worksheet columns does not help, the date problem is prior to the data reaching the sheet.

As per Scott's post, use the 'Text' option, and don't 'double-click' on .csv files, but rather use the Open command or rename the file to .txt to force the wizard.

If you do the same file regularly you should perhaps have a macro to import the file and save you the effort.

Cheers
---