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

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

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