Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I stop date formatting?
I have a spreadsheet that collects data that I load directly into a database.
There is a column, "eff date" which I use to collect what look like dates, but they are really strings (database requirement). I've set the number format for the eff date column to "Text". This is working as far as typical data entry goes. Example: Eff Date 2007-03-01 9999-12-31 2001-10-10 .... But when users do a find/replace on this column, and for example, replace all the 9999-12-31 values with 2010-12-31, it reformats the 2010-12-31 to 12/31/2010, changes the cell from "Text" to "Date". Then I can't import it into the database without additional manipulation. Is there any way I can programmatically through vba shut off this date formatting off on find/replace? I'm working with Excel 2003. -- Thanks, Bruce Bolio |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I stop date formatting?
I doubt it, Think would need to write a macro to loop throught the column,
making the change and preserving the value as text using the single quote. -- Regards, Tom Ogilvy "Bruce Bolio" wrote: I have a spreadsheet that collects data that I load directly into a database. There is a column, "eff date" which I use to collect what look like dates, but they are really strings (database requirement). I've set the number format for the eff date column to "Text". This is working as far as typical data entry goes. Example: Eff Date 2007-03-01 9999-12-31 2001-10-10 ... But when users do a find/replace on this column, and for example, replace all the 9999-12-31 values with 2010-12-31, it reformats the 2010-12-31 to 12/31/2010, changes the cell from "Text" to "Date". Then I can't import it into the database without additional manipulation. Is there any way I can programmatically through vba shut off this date formatting off on find/replace? I'm working with Excel 2003. -- Thanks, Bruce Bolio |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I stop date formatting?
That's what I wound up doing, I was hoping there was something more elegant.
Thanks! -- Thanks, Bruce Bolio "Tom Ogilvy" wrote: I doubt it, Think would need to write a macro to loop throught the column, making the change and preserving the value as text using the single quote. -- Regards, Tom Ogilvy "Bruce Bolio" wrote: I have a spreadsheet that collects data that I load directly into a database. There is a column, "eff date" which I use to collect what look like dates, but they are really strings (database requirement). I've set the number format for the eff date column to "Text". This is working as far as typical data entry goes. Example: Eff Date 2007-03-01 9999-12-31 2001-10-10 ... But when users do a find/replace on this column, and for example, replace all the 9999-12-31 values with 2010-12-31, it reformats the 2010-12-31 to 12/31/2010, changes the cell from "Text" to "Date". Then I can't import it into the database without additional manipulation. Is there any way I can programmatically through vba shut off this date formatting off on find/replace? I'm working with Excel 2003. -- Thanks, Bruce Bolio |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop cells formatting? | Excel Discussion (Misc queries) | |||
How stop auto formatting? | Excel Discussion (Misc queries) | |||
Stop automatic formatting of cells (number, text, date, etc) | Excel Worksheet Functions | |||
Is it possible to get Excel to stop formatting text as a date? | Excel Discussion (Misc queries) | |||
how to stop excel formatting | Excel Discussion (Misc queries) |