MrExcel Publishing
Your One Stop for Excel Tips & Solutions

date formatting problem

Posted by Chris Polonchek on November 13, 2001 11:32 AM

I am importing data into an excel spreadsheet, and some of the values that I am importing look like this: "4-23". Excel takes this value and assumes it's a date and changes the format of that cell to a date format, making it look like: "4/23/01". I don't want this to happen. I want it to automatically format the cell as text, so that the user only sees "4-23".

I have tried importing it as the value "'4-23" but this shows up in the cell with the apostrophe in front of it!

I am hoping there is some sort of function that I could add to the data, like "=formatAsText(4-23)" that would FORCE excel to leave it as text format.

Also, I know that I can go in manually and right click, go to format data and format it as text that way, but it's just not feasible in this situation, so I would like to take care of it in the import.

Thanks in advance for your help!


Posted by bob Umlas on November 13, 2001 12:36 PM

If you're using the Text Import wizard, which I assume you do because importing a text file brings it up automatically, then in step 3 there's an option to select a field and indicate it's text. That should solve your problem.

Posted by Chris Polonchek on November 13, 2001 1:14 PM

I'm not importing it through the text import wizard, i'm doing it through a third part piece of software.