 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Cell Auto Format
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? > 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. [quoted text clipped - 12 lines] > > Excel 2007 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). > Can you give an example of what data you are finding and what exactly you are > replacing it with? [quoted text clipped - 15 lines] > > > > Excel 2007  SignatureDave 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. > 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. [quoted text clipped - 12 lines] > > Excel 2007 SignatureDave 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. > 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. [quoted text clipped - 12 lines] > > Excel 2007 SignatureDave Peterson 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 [quoted text clipped - 20 lines] >> >> Excel 2007 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! [quoted text clipped - 39 lines] > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.