Stop auto correction of number into a date

Anonymous
2010-04-09T11:08:55+00:00

When i work with excel, i do data entry, some of the numbers that i type in can be interpenetrated  as dates. when this happens excel automatically changes the format of the cell from general, to date. i want to be able to stop this auto correction.

I know there are a number of ways to avoid this happening, but i am fed up with these methods. i simply what to turn the auto correct off, so that instead of what i type into the cell changing, what i type into the cell shows exactly what i typed in.

really hope there is someone out the who knows how to do this.

thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2010-04-09T17:09:58+00:00

If you format the cells as Text, all entries will be entered as text

(leading zeroes will be retain, date-like values won't be converted to dates, etc)

...CTRL+1 (to view the formatting dialog window)

...Select: Number_tab...Category: Text

That should resolve most (but not all) of the problem.

Does that work for you?

Ron Coderre

Microsoft MVP - Excel (2006 - 2010)

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

Was this answer helpful?

60+ people found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2010-04-09T16:55:47+00:00

Tim

I understand your frustration but this is not an autocorrect issue. 

Typing in 1/5 will automatically be recognised as a date as that is what it is unless... you signify that it is a formula and you expect the answer 0.2 to appear or it is a text item in which case you must preceed the data with a ' apostrophy.

In signifying the that the item is text you will have a data item which is left aligned, as all text is by default.  All other items in this column (even if purely numeric such as product codes) should aligned left.  Product codes though numeric should never be treaded like numbers, if you do, then expect issues.  Telephone numbers are similarly not true numbers, if they were treated as such, leading zeros would be dropped.

Though the whole idea of having to type in that extra character of ' is a pain to remember, it will make your data more acurate, reliable and portable.

Pat PS If you found this useful please vote. Thank you:¬)

Was this answer helpful?

40+ people found this answer helpful.
0 comments

311 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2015-10-15T14:33:15+00:00

    Very helpful post. I am hoping that at the very LEAST Excel 2016 will prompt with an import dialog when opening .csv files (instead of opening them and auto-formatting). Open Office has this behavior, so I often use OpenOffice when I need to look at .csv data.... Excel has many features that are lacking in OpenOffice, so I only use OpenOffice when I think/know Excel is going to totally f-up the formatting.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-10-15T12:55:16+00:00

    Your frustrations are shared by many -- just look at the length of this thread.  And although this forum is supported by MS, it is a peer-to-peer forum and not a place to get support from the company itself.  (Although sometimes company representatives do post here -- that is rare).

    The work-around that usually works for the kind of data you are describing is to do an Import.  In Excel 2007 and later:

    • Do NOT Open your txt or csv file
    • Instead, navigate to the Data Ribbon / Get External Data tab / From Text option
    • Select the .txt or .csv file you wish to import.
    • The Text Import Wizard will open up.
      • You probably want to select Delimited and Comma
      • At step 3 of the wizard, you will have the opportunity to designate particular columns as Text.  (The default is General).
    • Your date-look-alikes will now remain as text, and the file can be saved as a normal Excel which will retain the text attribute of the designated cells.
    • If you are manually adding data, note that only the cells with data in the imported column will have the text attribute.  So if you add data, you may want to pre-format the cell as text, or precede your entry with the single quote  '  which will not show up in the cell, but will instruct Excel to treat the data as text.

    I have heard a rumor that Excel 2016 might have the option to turn off this auto-formatting, but I have not seen anything definitive on that.

    If you are using a version of Excel prior to 2007, the Import option is on a dropdown in the File Open dialog box.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-10-15T12:24:12+00:00

    That's all nice and good if what you want to do is store the sales history of your frozen banana stand, but it is not a solution if you are working with biological analysis data. Our high throughput analysis softwares generate .txt and .csv files with thousands of rows of numerical data. Working with the tables is often more convenient in Excel, but since the BEFORE that you are referring to, Ron, does not exist, due to the fact that the files are automatically generated, the cells cannot be formatted to text BEFORE!

    Now it is of course possible to open a blank spreadsheet, format all cells to text, copy all data over to the new file from the .txt or .csv, in which case the formatting error does not occur. But the file needs to be saves to .txt or .csv again for the next analysis step, and if the file is now opened with Excel again, the auto-formatting will re-occur, since .txt and .csv files do not store cell formatting. And since these files are opened, modified and re-saved many times a day every day, following this procedure every time is extremely inconvenient and a waste of valuable company time.

    So, my question is this. Since turning of the auto formatting used to be an option in former versions of Excel, why are we given half-baked work-arounds that do not work for us (and anybody working with large sets of data, be it biology, physics, finance, medicine...), instead of just adding the option to turn off auto formatting in one of the many regular updates?

    And why is this auto formatting active by default in the first place, when it is actually more likely that a number will be converted to a date by mistake, rather than by design? In addition, if I want to write a date, I will write the date, and not a random combination of characters resembling a date. And from an earlier post from a different user, it has become clear that the auto formatting of dates is likely to NOT RECOGNIZE ACTUAL DATES! (Three posts up, posted by TonyCorless).

    So bottom line, this auto formatting is in the way of people working with large data, and not that useful for people who want to write dates, since it is safer to just write the date correctly.

    Any comments or suggestions?

    Was this answer helpful?

    0 comments