How do I remove the annoying automatic date formatting in Excel?
 

I use Excel to display/sort tables of data arranged in columns.

Some of ther columns are numeric, some are text.

One of the columns is giving me a lot of hassle. It should contain names of
genes, which Excel does okay, until you find names like "SEP7" or "MAR3",
which Excel transforms into a date form, usually "Sep-07", but sometimes into
a 5-digit number like "39326".

I have cancelled every auto-correct and auto-format option I can think of,
but no joy.

The only thing that works is format the whole thing as text.

Isn't there a way to avoid this "by default", so that Excel just shuts up
and takes what I type in as I type it, without changing anything? If it's a
number, it's a number, if not, it's text...

It's very annoying, because I have these tables with tens of thousands of
names, which I rearrange, merge, mix... when you import a column of names the
names are changed without warning. I don't want Excel to guess anything for
me.

If you know how to turn off this "feature" that appears loaded by default,
please let me know.

Thanks!

Jose

Answer: How do I remove the annoying automatic date formatting in Excel?
 

Hi Jose,

I understand your frustration with Excel's automatic date formatting. Fortunately, there is a way to turn off this feature and prevent Excel from changing your data without your permission.

Here are the steps to disable automatic date formatting in Excel:

1. Select the column(s) that you want to prevent from being automatically formatted.
2. Right-click on the selected column(s) and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, click on the "Number" tab.
4. In the "Category" list, select "Text".
5. Click "OK" to close the dialog box.

By following these steps, you have now formatted the selected column(s) as text, which means that Excel will no longer automatically format any data entered into those cells.

If you have already entered data into the column(s) and Excel has automatically formatted some of the entries as dates, you can convert them back to text by selecting the column(s), choosing "Text to Columns" from the "Data" tab in the ribbon, and then selecting "Text" as the column data format in the "Convert Text to Columns Wizard".

How do I remove the annoying automatic date formatting in Excel?
 

The feature cannot be turned off. Your best bets are using Text format, as
you mentioned, or preceding the entry with an apostrophe.

--
Jim
"Jose-Edinburgh" wrote in message
...
|I use Excel to display/sort tables of data arranged in columns.
|
| Some of ther columns are numeric, some are text.
|
| One of the columns is giving me a lot of hassle. It should contain names
of
| genes, which Excel does okay, until you find names like "SEP7" or "MAR3",
| which Excel transforms into a date form, usually "Sep-07", but sometimes
into
| a 5-digit number like "39326".
|
| I have cancelled every auto-correct and auto-format option I can think of,
| but no joy.
|
| The only thing that works is format the whole thing as text.
|
| Isn't there a way to avoid this "by default", so that Excel just shuts up
| and takes what I type in as I type it, without changing anything? If it's
a
| number, it's a number, if not, it's text...
|
| It's very annoying, because I have these tables with tens of thousands of
| names, which I rearrange, merge, mix... when you import a column of names
the
| names are changed without warning. I don't want Excel to guess anything
for
| me.
|
| If you know how to turn off this "feature" that appears loaded by default,
| please let me know.
|
| Thanks!
|
| Jose