I have a large group of cells (48) whose format is text, with data that looks
like a date (1-9-01) but is not actually a date.

When I use the replace function to change -9- to -10- it changes the format
of the cells from text to date. How do I keep the replace function from
overriding my chosen settings, without altering the way my data appears?

You can format all the cells first as Text, and then the change to dates
should not occur.

But, do you really need to use the hyphen? You could search all the cells
for hyphen and replace with underscore (or full-stop), and then if you
subsequently search for _9_ and replace with _10_ (or .9. to .10.) this
should not invoke a change to dates.

Hope this helps.

Pete

"FP Novice" wrote in message
...
I have a large group of cells (48) whose format is text, with data that
looks
like a date (1-9-01) but is not actually a date.

When I use the replace function to change -9- to -10- it changes the
format
of the cells from text to date. How do I keep the replace function from
overriding my chosen settings, without altering the way my data appears?

I think that a change from the hyphen is the only resort as it overrides my
formatting everytime. The only way to keep excel from assuming it is a date
is to remove the hyphen (as near as I can tell anyway).

Thanks Pete

"Pete_UK" wrote:

You can format all the cells first as Text, and then the change to dates
should not occur.

But, do you really need to use the hyphen? You could search all the cells
for hyphen and replace with underscore (or full-stop), and then if you
subsequently search for _9_ and replace with _10_ (or .9. to .10.) this
should not invoke a change to dates.

Hope this helps.

Pete

"FP Novice" wrote in message
...
I have a large group of cells (48) whose format is text, with data that
looks
like a date (1-9-01) but is not actually a date.

When I use the replace function to change -9- to -10- it changes the
format
of the cells from text to date. How do I keep the replace function from
overriding my chosen settings, without altering the way my data appears?

Glad to be of help.

Just replace it with a character that allows you to read it but is not
recognised by Excel as a date (or time) delimiter.

Pete

On Feb 29, 7:51*pm, FP Novice
wrote:
I think that a change from the hyphen is the only resort as it overrides my
formatting everytime. The only way to keep excel from assuming it is a date
is to remove the hyphen (as near as I can tell anyway).

Thanks Pete



"Pete_UK" wrote:
You can format all the cells first as Text, and then the change to dates
should not occur.

But, do you really need to use the hyphen? You could search all the cells
for hyphen and replace with underscore (or full-stop), and then if you
subsequently search for _9_ and replace with _10_ (or .9. to .10.) this
should not invoke a change to dates.

Hope this helps.

Pete

"FP Novice" wrote in message
...
I have a large group of cells (48) whose format is text, with data that
looks
like a date (1-9-01) but is not actually a date.

When I use the replace function to change -9- to -10- it changes the
format
of the cells from text to date. How do I keep the replace function from
overriding my chosen settings, without altering the way my data appears?- Hide quoted text -

- Show quoted text -