Text format inconsistant

[Rob]

When I export a spreadsheet to a text file and open it via Excel (ie I right
click the text file and select Open with Excel), one of the cells (of many
like it) where I have a value such as 9-5510 are shown in a date format as
Sep-10 (the formula bar says 1/09/5510). The text file opened in Notepad
shows the same cell as 9-5510 which is as it should be.

Yet the value 1-1111 shows correctly in both the text file and if opened via
Excel.

The spreadsheet that that the export was done from has all these cells
formatted as General.

Rob

 

[JE McGimpsey]

1-1111, which might represent January of 1111 cannot be made into a
valid XL date, since the year would be less than 1900, so the parser
retains it as Text.

 

[Rob]

Yes, that makes sense. However, why doesn't it stay as 9-5510 even if I go
through the process of opening the text file from Excel and select the text
format for that column? If I'm opening a text file, surely Excel should
respect that values there-in should retain a text format.

Rob

 

[David Biddulph]

When I open such a file from Excel *and select the text format for that
column* it does stay as 9-5510. If I open the file *without* specifying the
format of the column, it will change it to a date. "Open With" will go
through the default route, so you need to go into Excel and use the "Open"
command to let you set the column format after you've defined the
delimiting.

That's with Excel 2003. Which version do you have that behaves differently?

 

[Rob]

I have 2007 now, but the same ocurred on XP (is that version 2002??)

Anyway, I did choose the latter procedure and set that column to text and
it still comes out date.

Rob

 

[Rob]

Sorry David,

I tried it again and seems to be OK now. One of the text files I had must
have been created from the incorrectly formatted Excel file and I was trying
to import bad data.

Rob