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.

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

"Josh" <Josh@discussions.microsoft.com> wrote in message
news:4A787659-E640-431E-A6F0-DA0644064B41@microsoft.com...
> 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

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