Cell format changes
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? |
Cell format changes
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? |
Cell format changes
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? |
Cell format changes
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 - |
All times are GMT +1. The time now is 09:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com