Excel keeps converting text to date format

[John T via OfficeKB.com]

How can I keep Excel from changing the format of a cell from "Text" to "Date?
" I use Excel 2002 SP3. I am entering a column of file names of the form
"01-01-01." Excel immediately presents that as "1/1/2001" So I then change
the format of the cell to "Text" and it changes the cell entry to "36892",
the day equivalent. So, I format the cell as text before typing in the entry,
and, as I would hope, it gives me "01-01-01" Now the frustration begins.
The file names have a repeating pattern, so I want to be able to copy a block
and do a "Find and Replace" on one part of it - for example to change "01-01-
01" "01-01-02" etc to "01-02-01" "01-02-02" etc. I copy the block of values
into a block of cells pre-fomatted as dates, and they present correctly.
When I do the Replace, however, Excel automatically changes the format of the
cells whose values I'm replacing to date, so instead of "01-02-01" I get
"1/2/2001" Is there a way to turn off this excruiatingly annoying "smart"
feature or do I have to type in all my text values preceded with a single
quote? Seems like I was doing that with the first version of Supercalc, but
I hoped we had progressed since then.
John

 

[Bill Martin]

How can I keep Excel from changing the format of a cell from "Text" to "Date?
" I use Excel 2002 SP3. I am entering a column of file names of the form
"01-01-01." Excel immediately presents that as "1/1/2001" So I then change
the format of the cell to "Text" and it changes the cell entry to "36892",
the day equivalent. So, I format the cell as text before typing in the entry,
and, as I would hope, it gives me "01-01-01" Now the frustration begins.
The file names have a repeating pattern, so I want to be able to copy a block
and do a "Find and Replace" on one part of it - for example to change "01-01-
01" "01-01-02" etc to "01-02-01" "01-02-02" etc. I copy the block of values
into a block of cells pre-fomatted as dates, and they present correctly.
When I do the Replace, however, Excel automatically changes the format of the
cells whose values I'm replacing to date, so instead of "01-02-01" I get
"1/2/2001" Is there a way to turn off this excruiatingly annoying "smart"
feature or do I have to type in all my text values preceded with a single
quote? Seems like I was doing that with the first version of Supercalc, but
I hoped we had progressed since then.
John

Try formatting the column of cells as text *before* entering the data...

Bill

 

[John T via OfficeKB.com]

Yes, I did that. The problem is that when I do a "Replace" (but not when I
type in a new value), Excel undoes that and reformats it as date. I think it
must be a bug in the "Replace" function, and I'm wondering if there's a
workaround other than preceding every entry with a single quote.

John

 

[Bill Martin]

Yes, I did that. The problem is that when I do a "Replace" (but not when I
type in a new value), Excel undoes that and reformats it as date. I think it
must be a bug in the "Replace" function, and I'm wondering if there's a
workaround other than preceding every entry with a single quote.

John

-------------------

Not that I'm aware of. One solution would be to write a macro that corrects the
error after the fact. After doing all your Replaces you'd hit a button to
automatically find any entries in the wrong format and correct them back again.

It's an inelegant solution, but it is a solution...

Bill

 

[Bill Martin]

Yes, I did that. The problem is that when I do a "Replace" (but not when I
type in a new value), Excel undoes that and reformats it as date. I think it
must be a bug in the "Replace" function, and I'm wondering if there's a
workaround other than preceding every entry with a single quote.

John

-------------------

Not that I'm aware of. One solution would be to write a macro that corrects the
error after the fact. After doing all your Replaces you'd hit a button to
automatically find any entries in the wrong format and correct them back again.

It's an inelegant solution, but it is a solution...

Better yet, write your own custom "Replace" macro that works properly. And add
whatever tweaks make it even better for your own particular problem. I agree
this should not be necessary in an ideal world, but IMHO it's the cleanest solution.

Bill