Stop auto correction of number into a date

it is not Norwegian Excel date format.  In 2003 you could work around this for all sheets and especially if you created a master sheet, so it always uploaded your features and not the MS defaults.

 

Unfortuantely they screwed with this feature in 2007 and maintained the @#@%#@ 2010 and will probably defend themselves. 

 

Interesting.  I've never used a worksheet or workbook template so have no experience there.  I have read that Open Office treats date data differently but I've never used it.  Perhaps it is something that might work better for you.

EDIT:  I just checked my copy of OO and it does not seem to have an option to disable that "feature".   Doing a web search for OO likewise reveals the same complaints.  There is one difference in that, with OO, copy/pasting text (at least from a Notepad file), brings up the Text to Columns wizard BEFORE pasting, so that differential columnar formatting can be applied at that point.  In order to do that in Excel, you need to use the Data Import from Text file feature.   (Get External Data  From Text)

I didn't mean to seem condescending.  Sorry it came off that way.  But clearly, a specification that has been present for years, and been complained about for years, is not going to change any time soon.  So at this point in time, it seems your only choices are to develop a work-around, or use a different tool.  I have read that Open Office does not have this same "feature", but I have not tried it myself.

This has been complained about here and elsewhere for as long as I can recall.  And MS has not deigned to change the behavior.  Most posters here have no connection  with MS, other than as users of their products, and a desire to help others.

If you read my suggestion closely, you will note that I did not suggest the workaround that you seem to be applying of pre-formatting a sheet as text and then copy/pasting and then using the Text-to-Columns feature of the Data tab.  Rather I suggested using the Data Import (also on the Data tab, but in a different spot) feature which allows you to format only selected columns as text prior to importing a text or CSV file.  (Get External Data  From Text)

Please try that method and see if it works for you.  If it does, perhaps you can adjust your vlookup formula to search for text strings in the "date-lookalike columns" and numeric values in the other columns.  Or provide some data samples and environment descriptions of where you are having the problem so perhaps a less cumbersome workaround can be suggested.

EDIT:  I just checked my copy of OO and it does not seem to have an option to disable that "feature".   Doing a web search for OO likewise reveals the same complaints.  There is one difference in that, with OO, copy/pasting text (at least from a Notepad file), brings up the Text to Columns wizard BEFORE pasting, so that differential columnar formatting can be applied at that point.  In order to do that in Excel, you need to use the Data Import from Text file feature.   (Get External Data  From Text)

If you want to use Excel, you will need to learn to work within its specifications.

To prevent Excel from modifying your data, you will need to pre-format the cells as text.  If you are importing a file, however, you cannot just "open" the file.  You will need to ensure the Text to columns wizard opens before you import the file.  In order to do that, you will need to select "Get External Data / From Text" under the Data Ribbon.

This behavior has been present in Excel for many versions, at least in the US version.  I cannot explain your observations regarding the Norwegian version.

Gee Ron, I find your response quite defensive as if you were the one personally responsible for this design flaw yourself.  Also, pretty offended by the condesension of your tone.  Just because folks point out a bug with the program (that obviously more than a few users are taking issue with), you seem to suggest that this is merely a result of "operator error" and that essentially we all need more training with Excel?  I would say that people who are power users, working with macros, and depend on the program day in and day out to complete their work ARE working within the programs specifications, however unsuccessfully.  Unfortunately the bottom line is that the current specifications do not adequately meet the needs of the end users.  Perhaps if you put as much passion into fixing the problem as defending the status quo, this thread wouldn't be dragging on for years and years without resolution.......

As for your solution listed above, I have tried this as well. I begin with a spreadsheet that is pre-formatted as text.  I then copied and pasted my data as values only, as to retain the text formatting but move the data to the new sheet.  At that point, I would use the "Text to Columns" feature to remove the apostrophe from my CSV generated part codes.  I then have the option within that feature to keep Text or change to General.  Changing to General results in the same issue of numbers auto-converting to dates.  Keeping the column as Text then results in the VLOOKUP issues I discussed previously, returning a blank cell for those items.  All in all, a no-win situation for me, and also proves that particular solution obviously doesn't work in all situations.  The funny thing is, a simple check box to disable the feature would fix the problem for everyone's use in a very uncomplicated way.  I don't understand what the resistance is.  It reeks of arrogance set forth by the programmers of Microsoft, incapable of admitting their work can be improved upon by creating additional features for the program.