Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
John T via OfficeKB.com
 
Posts: n/a
Default Excel keeps converting text to date format

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
  #2   Report Post  
Bill Martin
 
Posts: n/a
Default

John T via OfficeKB.com wrote:
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
  #3   Report Post  
John T via OfficeKB.com
 
Posts: n/a
Default

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 wrote:

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

Bill



--
Message posted via http://www.officekb.com
  #4   Report Post  
Bill Martin
 
Posts: n/a
Default

John T via OfficeKB.com wrote:
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 wrote:

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

Bill

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

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
  #5   Report Post  
Bill Martin
 
Posts: n/a
Default

John T via OfficeKB.com wrote:
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 wrote:

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

Bill

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

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Date Format - users should be able to override it automatic. jamezog Excel Discussion (Misc queries) 7 May 20th 10 02:45 PM
Excel shouldn't bitch about a "date format" when formatted as txt Ibbits Excel Discussion (Misc queries) 5 July 18th 05 05:05 PM
EXCEL - Please add a date format - yyyy mmm dd (mmm being letters I don't know but probably Lorraine O'Del Excel Worksheet Functions 4 July 9th 05 11:34 PM
Format date in excel 2000 like '31st January 2005' Malcolm Agingwell Excel Discussion (Misc queries) 1 June 29th 05 11:01 AM
How do I convert a number formated as a date to text in Excel? BrotherNov Excel Discussion (Misc queries) 5 March 2nd 05 04:51 PM


All times are GMT +1. The time now is 04:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"