Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Date Format - users should be able to override it automatic. | Excel Discussion (Misc queries) | |||
Excel shouldn't bitch about a "date format" when formatted as txt | Excel Discussion (Misc queries) | |||
EXCEL - Please add a date format - yyyy mmm dd (mmm being letters | Excel Worksheet Functions | |||
Format date in excel 2000 like '31st January 2005' | Excel Discussion (Misc queries) | |||
How do I convert a number formated as a date to text in Excel? | Excel Discussion (Misc queries) |