Cell Auto Format

  • Thread starter Thread starter Justin Rich
  • Start date Start date
J

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
 
Can you give an example of what data you are finding and what exactly you are
replacing it with?
 
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.
 
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.
 
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).
 
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
 
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.

Justin said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top