Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I turn off the autoformat that converts 1-0 to Jan-00?
And all the other number to date stuff. It's getting really annoying having
to go back and change every cells format. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I turn off the autoformat that converts 1-0 to Jan-00?
I don't think there is a way of doing it. I could be wrong. The work around is to put a ' before your data -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=547912 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I turn off the autoformat that converts 1-0 to Jan-00?
"Josh" wrote in message
... And all the other number to date stuff. It's getting really annoying having to go back and change every cells format. Format the cell as text before you enter the data. -- David Biddulph |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I turn off the autoformat that converts 1-0 to Jan-00?
Josh wrote: And all the other number to date stuff. It's getting really annoying having to go back and change every cells format. Ah, Josh... I posted the same question in several forums this week, after months getting annoyed with this issue which nobody could solve. I still can't. It seems that Excel, nice as it is, seems to insist to "guess" this date thing for you, and will not let you turn the behaviour off. In my case, I have tab-delimited txt files with thousands of lines and tens of columns. Soem columns contain names of genes, and some are called DEC10, SEP7... other ID codes are in teh form 3-24... all those get converted automatically, and as a result my swearing abilities have improved enormously. I am fluent now in three languages. The "solution" (it really isn't) I have found for my data, is to add a blank space in front of every datum in the columns that I expect will contain problems. This seems to be enough for Excel to shut up and do as it's told, and show me " DEC10" rather than "10-Dec"... Of course, I still prefer "DEC10", but having a space in front is not too bad. It seems to have the same effect as adding an apostrophe, but at least you don't see it when you produce tables etc. I do that in all my source files, and then I'm okay. This is because I do my number crunching and arranging outside Excel (I use R). If you want to use Excel, you can import tab-delimited txt files, from within Excel. The Wizard gives you the option to mark certain columns as text, which will preserve your "1-0" as you want it, and not as Excel thinks it should be displayed. If you want to enter the data manually, then you can mark a range, or a whole column, or whatever, as text (format cell menu)... and when you write "1-0" it'll keep it as "1-0". But you'll have to do that for every sheet, every range. If the layout is always the same, you could create a template sheet with the formats already in place. Still... we shouldn't have to work around Excel's shortcomings... especially an obvious one as this. Any "guesswork" and autocorrecting shold be an *option* that you can turn on and off. Jose |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make it easy to turn off the little paste option graphics | Excel Worksheet Functions | |||
How do I turn off autoformat for .CSV (comma delimited) files? | Excel Discussion (Misc queries) | |||
How can i get autoformat name of range in excelsheet? | Excel Worksheet Functions | |||
How do I turn off ALL autoformat in Excel ? (I can't even type "J. | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |