Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Can cell format come from and change with reference cell format | Excel Discussion (Misc queries) | |||
Excel Auto-Format cell to email format | Excel Discussion (Misc queries) | |||
How do I copy data in single cell format to a merged cell format | Excel Discussion (Misc queries) |