Text format inconsistant
 

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

Text format inconsistant
 

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.

In article ,
"Rob" wrote:

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

Text format inconsistant
 

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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Rob" wrote:

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

Text format inconsistant
 

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?
--
David Biddulph

"Rob" wrote in message
...
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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Rob" wrote:

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

Text format inconsistant
 

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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?
--
David Biddulph

"Rob" wrote in message
...
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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Rob" wrote:

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

Text format inconsistant
 

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

"Rob" wrote in message
...
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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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?
--
David Biddulph

"Rob" wrote in message
...
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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Rob" wrote:

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