Search & Replace Changes Cell Formatting

[So_Long]

I typed in a series of dates in the following format:

x08-07
x12-01
x07-04

I simply typed in x to save time. The idea was to replace each instance of x with 2007- when I was finished, so the list would look like this:

2007-08-07
2007-12-01
2007-07-04

But when I do a Search & Replace operation, it automatically changes each cell's formatting from Text to Date, and my entries change from 2007-08-07 to 07/8/07, or something like that.

It works fine if highlight each cell individually, delete the x and type 2007- in place of it. But I have a LONG list of dates to edit. Is there some way to do a search and replace operation without changing each cell's formatting?

Thanks.

 

[Bob Greenblatt]

I typed in a series of dates in the following format:

x08-07
x12-01
x07-04

I simply typed in x to save time. The idea was to replace each instance of x
with 2007- when I was finished, so the list would look like this:

2007-08-07
2007-12-01
2007-07-04

But when I do a Search & Replace operation, it automatically changes each
cell's formatting from Text to Date, and my entries change from 2007-08-07 to
07/8/07, or something like that.

It works fine if highlight each cell individually, delete the x and type 2007-
in place of it. But I have a LONG list of dates to edit. Is there some way to
do a search and replace operation without changing each cell's formatting?

Thanks.

Did you try changing the format first? Use a custom format of "yyyy-mm-dd".
It works fine for me.

 

[So_Long]

Thanks for the tip. I don't see that one listed among the custom formats, but I'll check again.

In the meantime, I discovered a workaround. I copied the data into a text document (Apple Pages), did the search and replace, then pasted them back into Excel. It worked perfectly.