If pasted text is auto converted to a date: _save_the_original_text_. Then give it back when text is desired. Don't corrupt data!
When people in computational biology paste a list of gene names sometimes genes like "SPET8" are in the list. The original value is lost, and now the column has corrupt data. (Why did you kill the original value?) Since the column of gene names usually goes off screen and is often in alphabetical order nobody notices this until something breaks in a bizarre way in another person's hands. Scientific papers have had corrupt data for this reason many times, and everyone advises each other to avoid excel for this reason. It might even make a difference for a clinical diagnosis. This is actually serious. Here's the first google result: http://manuelcorpas.com/2011/11/05/beware-of-gene-names-in-excel/
Alternatively, if a column of text is pasted which has an overwhelming majority of what can only be text values, and an underwhelming minority that could be dates OR text, then prefer text. The probability that I wanted to delete my text and replace it with garbage is 0. Nobody ever wants that to happen, and I'm sure there are many other examples of this biting people in the ass for all sorts of excel users.
Noticed you guys were doing an AMA. Figured this would be the perfect time to bring it up!
Thanks for the suggestion. We’ll prioritize this based on the number of votes it gets, so be sure to keep voting!
John [MS XL]
John, This is the same problem as "Allow us to turn off automatic conversion to date", please combine
Caleb McNevin commented
Microsoft: Though number of users who've shown support is a good metric in most cases, this case is not one of them. You're never going to get the thousands of votes on this issue that you get on others due to the fact that biologists and people working in medical research are a minority compared to people in business roles. Please consider the impact of following a single metric, and consider bumping this issue up to a higher priority.
Kasperi Korpinen commented
A program must never modify data content without the consent of the user and without warning the user that the program has corrupted the data!!! All these data converting and formating tools excel has can be usefull in some situations, but they should be made optional and gathered into a tool box for datamanipulation tools (eg. text to numbers etc...).
** Our value of Pi is 2 rather than the generally accepted value of 3.14......
We will consider changing this if we get enough votes..... really?
I've just run into this opening a .csv file in excel. The .csv file contains a list of oil and gas well data. Well names are formatted using "/" & "-" as delimiters. Where well names can be parsed into date format Excel does this automatically when it opens the .csv file and losses the original value.
eg, Well 1/04-1 is converted into 01/04/2001
And then when i attempt to revert it back to its original value (text value) it returns 36982
This is another 'feature' that should not be prioritized on votes - it should bloody well be fixed!
William Farrell commented
There are many options to disable various auto-formatting in Excel, however a persistent problem has been the auto-conversion of numbers that resemble dates into Excel's own proprietary date serial number. This is more than mere formatting as it changes the actual value of the cell! This is a long-standing problem, dating back in forums for at least a decade (http://geekswithblogs.net/Patware/archive/2005/10/11/56647.aspx) and actually causes serious problems in scientific research (http://geekswithblogs.net/Patware/archive/2005/10/11/56647.aspx). Simply changing the cell format to "text" is insufficient when importing data into Excel (say from .csv) since, as mentioned, the values have been irreversibly altered from their true value to some arbitrary serial number. This seems to me to be the easiest problem in the world to fix: a simple check box in Options to uncheck "auto-convert dates", yet this known issue has not been fixed over numerous releases. Can something please finally be done about it?
The Paste Button should always have available the KEEP SOURCE FORMATTING regardless of the source.
A much simpler but similar submission is this one:
Maybe you should count those votes towards this one as well!
Delaney Waldron commented
Losing data when pasting makes me use google sheets. I don't know what to do so I switch products instead. Data is important in my job.
I second this request. This is also true when working with times that have more precision than Excel likes to display. It pretty much disappears from the system. Say I'm working with microsecond precision, and I enter a time to that level. The resultant displayed, then fed through subsequent calculations is truncated to whatever the Excel developer chose it to be. (probably something like an F64, but based in days, not seconds)