Cell Auto Format

[Justin Rich]

im doing a find and replace on some data and once its done its auto
formating the data to a Date field and this is not what i want.

How do i stop Excel from auto formatting the cells?

I tried to set the format and then do the find and replace and it still
changed them. Also i messed around with the advanced options of hte replace
a bit because you can pick formatting info but i wasnt able to figure out
how to get that to work.

any help would be greatly appreciated.

Thanks
Justin

Excel 2007

 

[Flick Olmsford]

Can you give an example of what data you are finding and what exactly you are
replacing it with?

 

[Dave Peterson]

Maybe you could try this (but save your work so you can close without saving if
it destroys your data).

in xl2003 menu system:
Tools|Options|Transition tab|check transition formula entry

Then do your edit|replace

If it worked, whoopee!

If not, then either edit|Undo or close without saving.

And remember to turn that setting back.

 

[Dave Peterson]

ps. With that setting unchecked, excel will convert anything that looks like a
date (after the replace) to a date.

You may be able to have a macro that can avoid the problem, but that would
depend on what the data looks like and what you want after the change.

 

[Dave Peterson]

If you put:
0000A12-25
in a bunch of cells

And then select those cells
edit|replace
what: 000A
with: (leave blank)
replace all

Then excel will see the 12-25 as a date (if your windows short date setting is
set to mdy order).

 

[Justin Rich]

i find it odd there isnt a way to turn this off.... i dont always want a
date when i type in 8/16!

what im doing is working with network equipment and the form i have says
8/16 [all] which means switch 8 port 16, not aug-16.

what i ended up doing, and i wish this wasnt the solution, was replacing the
/ with a , and then removing the all, and then spliting the cells based on
the ,

kinda of stupid, but the problem was solved.

Thanks guys
Justin

 

[Dave Peterson]

If you're typing the value, you can preformat the cell as text or start your
entry with an apostrophe: '8/16

If your data was really: 8/16 [all]
you could use a formula in a helper column:
=left(a1,search(" ",a1)-1)

Or you could have used the tools|Options|transition tab and the edit|replace to
get rid of the " [all]"

So there is more than one way to avoid excel's helpfulness.

i find it odd there isnt a way to turn this off.... i dont always want a
date when i type in 8/16!

what im doing is working with network equipment and the form i have says
8/16 [all] which means switch 8 port 16, not aug-16.

what i ended up doing, and i wish this wasnt the solution, was replacing the
/ with a , and then removing the all, and then spliting the cells based on
the ,

kinda of stupid, but the problem was solved.

Thanks guys
Justin

ps. With that setting unchecked, excel will convert anything that looks
like a
date (after the replace) to a date.

You may be able to have a macro that can avoid the problem, but that would
depend on what the data looks like and what you want after the change.