Allow us to turn off automatic conversion to date
Search for "make excel stop converting numbers to dates" and you will see the user demand for this feature. Please for the love of all that is holy let this be at least an advanced user setting.
Thanks for the suggestion Jessica. We’ve got a few other suggestions on the site that are similar with scientific notation, etc. We’ll take a look at this area, and pay special attention to any cases that get a lot of votes. So please keep the votes coming to help us do a good job at prioritizing asks like this!
John [MS XL]
either i don't understand what you are saying, or you don't understand how OpenOffice works.
why CAN'T you just use OpenOffice and ditch MS Office? you said you need to update existing spreadsheets that you've made...well, OpenOffice can do that. you can open ANY Excel spreadsheet with no problems.
the only difference is that it will only save as the old XLS files and not XLSX format, but the content is the same. and it DOES open XLSX files no problems.
the only potential issue might be with some proprietary Excel stuff (other than just data in a spreadsheet) that only Excel can do, but 99.9% of users don't even use/need that stuff.
if you need it, then i can see why, but otherwise, am i missing something?
I don't expect this solution will help many but it works for me because I am just a home user of excel for various interests.
The habit of excel changing numbers to dates, however you try to stop it, is so frustrating I had to find a way to allow me to copy something with numbers in it from the internet and paste it into excel without all of the numbers being converted to dates.
All of the suggested solutions fail to solve the problem because all of the text conversion ideas then prevent the numbers being used in calculations (unless I have missed something?)
My long winded solution (how crazy can you get) was to install OPEN OFFICE. (FREE!)
I can copy and paste data from the internet into an open office spreadsheet and it keeps the numbers as it should.
I can then copy and paste that data into excel and surprise surprise it keeps the numbers no change to dates !!
(make sure the open office sheet is not left at read only)
That works so I hear you say why not just use open Office and ditch MS?
Well over the years I have developed dozens of spreadsheets and at times I need to update them with new data from the internet or other programs I am using.
I do not wish to destroy years of my efforts just because some one in MS does not care about the problems they give their users.
Spreadsheets were originally developed as accountancy or number crunching aids it is a pity that ability now seems to be lost in the latest (help the user) versions of Excel
The sort of solution of formatting cells or other suggestions are about worthless for CSV files.When I open CSV file, it is already "fixed" by Excel and there is no UNDO anymore
In my cases (and seeing # of other comments) this is considered data corruption - there is no beating around the bush as this what that is.
I have no way to know which cells to fix either. I have painful workaround - open the CSV file in Excel and set all fields as text and open it from some other pure text editor - copy from text editor (the whole thing) and in Excel use text import option to overwrite that
Did I mention that it is painful?
Carmel Abramovitch commented
I sort of solution and a chat with support ;)
I hate to say it (and it pains me a LOT) but they don't care and no amount of complaining and providing proof will change that. The only way in my opinion is to hit their bottom line - sales. Unfortunately even if we and all our friends/family stop buying Microsoft products we would make an invisible to Microsoft difference in sales - just too few of us. if by some miracles Microsoft notices the difference, they still would not know that this is because of this stupid design (and this is not the only one).
They are (like just about any other software company) busy adding support for emojis and connections to Facebook, Tweeter and other social app
I still vouched to myself that I will do my best to never buy Office product from Microsoft ever again
One wonders how Microsoft would react if we arbitrarily changed the currency used to buy their products while keeping the price the same. 100 pesos/100 dollars/100 marks/100 nickels, it's still 100..
Others have said it all. It is a total disregard of a users requirements to arbitrarily alter their data without permission. One might hope that there was a government Quango that might intervene or a trading standards unit that could declare the software not fit for purpose
click the cell in the top left corner, above "1", and to the left of "A".
right-click any cell.
click Format, and change the format to Text.
now simply save this as book.xltx (Excel Template File *.xltx) in the following directory:
or wherever your XLSTART directory is.
Excel will never change your text to numbers again.
...at least till the next update.
Vítek S commented
Changing data format automaticaly is very annoying, please give as the chance to change this.
Hello, why, for God's sake, does Excel keep changing the data that I write to a cell? I want to write something and I expect that the same thing will stay there!!! This is of course related to the stupid automatic conversion of anything to a data. But also, another problem is when I have a number and a letter in a cell, I later delete that letter and then suddenly the number (which could be a telephone number, invoice number, order number or whatever) appears with a dot a two zeros behind it. Why does it add decimal places when I did not type them? The thing is that it automatically changes the format of the cell to a number. Why? Why, for god's sake, why???
PS: I use Excel for Mac. This issue is the same for any version of Excel, unfortunately...
don't you know, D? "advanced users" use Macs! lol
seriously though, i have found that MOST of the "simpler users" have never even used Excel. they had no idea what it did, what it was for, or how to use it.
honestly, i'm not sure I know anymore...
There has been a trend for few years to have things more user friendly (in computer related industry, not only Windows). In theory this is good thing but in general I am finding the execution of that very poor (in general with only very few exceptions). Also, there is trend in Windows to start to hide or even remote some of the advance options. Office is following that too. Due to what I mentioned, they are more and more targeting simple users and in some way don't give a **** about advanced ones anymore.
I am really sick and tired of the changes in computer industry (including smart phones which I find to actually be getting dumber in lots of ways) - the trend to try to anticipate my needs and proactively help with no option to disable is really common and getting worse.
We can voice this problem all we want and I doubt anything useful will come out of this. This only way to try to fight it is to stop using their products. I promised myself that I will do my best not to upgrade Office ever again on my home systems - I will look into all other possibilities first.
I work in computer industry and I started to hate it due to all the dumb changes that are happening
Microsoft can fix the issue, they just choose not to. Basically there are two groups of people. Us professionals who work with data for a living who see 1-1 as just that, 1-1 AND people who do not work with data for a living but find it helpful to have Microsoft think for them. They see 1-1 as.."hey I think this person is trying to enter a date, let me change the data for them because they are too lazy to finish entering two more numbers themselves. Microsoft has chosen to side with non-data users and automatically alter thousands of your clients records without telling you. I have seen people lose their jobs over this incredibly irresponsible decision. Is it really worth people hating your product because you cant simply add an option to turn this off Microsoft? At no point should a software EVER alter ones source data. 1-1 is not 1/1/2020 (or whatever year this post is read at in the future)
1-1 changed to 1-1-2020 = someone is going to lose their job if they aren't paying attention.
I'm sick of this, it's been an issue for ages and still not fixed. I don't get why the default is to automatically corrupt your data, with no option to turn it off.
it took the addition of code to try to "outsmart" users.
but apparently, trying to remember where they put that code is not easy.
i will say this only once: no program that i trust will try to change the data i give it.
Microsoft lost what little trust i had for them. i am now happily using OpenOffice, or Google Sheets when i need to share it, as the other user does not need to even have any program installed to view it!
@Anonymous: In the meantime while waiting for retirement, I mean for them to fix this, you CAN import CSV's with the old Import Wizard which did allow you to specify a column's type BEFORE import. Not nearly the same as it coming up just by clicking the file to open it, but "in the meantime"... I repeat a Comment I made for a different Suggestion:
I've lucked across the Dead Sea Scrolls of opening a CSV in "the old manner" in the second answer in a Stack Exchange (Super User) answer:
—— Slightly modified, just the last step ——
(This is different from the version of the old wizard that is presented in the Text to Columns feature in that it still works to do the actual importation, not just to break up already imported or existing data. Same tool, just allowed to act at the moment of importation instead of only after the damage is done.)
Basically, go to Options and make sure that in the selections under the Data heading (at the bottom of them in the "Show legacy data import wizards" section and make sure the third one (at least, required for this... but I've always checked them all, eh?) is selected.
Then, to use the old import wizard the way we all remember it working, go to Data in the Ribbon, choose the first choice on it: "Get Data", and choose "Legacy Wizards" from the dropdown menu.
Here's a nasty foolishness on their part: Pick "From Text (Legacy)" from the dropdown new menu. The foolishness comes from how many people won't know that isn't just .TXT files, but several types of such including .CSV files (and, well, literally anything you think you'd like to try this on for that matter). Anyway, pick it and use the file browser that pops up to find your file and select it.
NOW you finally have the old wizard, functioning precisely as we remember it, and BEFORE it opens the CSV file so BEFORE it destroys information forever. Do like you always did, select "Delimited" and move on, make sure "Comma" is a selected delimiter (really, make it the only one to avoid hassles), and then the magical step... click "Next" to get to the step in which you tell Excel how to import the various columns: as General (it will hammer you if it can find a way), Text, Date (for various layouts), and Skip. (If you are using a different delimiter, you can do a couple other things, but they are not germane to CSV importation.)
Finally, click "Finish" and you're... well, almost home free.
Here's where you'll probably diverge from the Super User answer which assumes you want a continuing connection and a Table. If you do, just choose options as you wish, or don't change anything, just click "OK" and you have a nicely imported Table from the CSV which keeps leading 0's and so on (if you marked that/those column/s to import as Text).
If you JUST WANT A FREAKING BLOCK OF IMPORTED DATA, do one last thing: Uncheck the "Add this data to the Data Model" checkbox (just above the lower left corner's "Properties" button). Now your import will truly be like the old days, just a slick import of the CSV's data with you having had the ability to force the importation approach for any column you wished to do so for. Had date-like data but they weren't dates? You marked them Text and now you're a WINNER! Ruined data is your fault only, like it should be!
I think we have to face the fact that Microsoft is never going to make what should be a very simple fix (just enable open csv preferences of "do these conversions" with date, scientific notation, etc). If they haven't done it by now, they are telling us that we just shouldn't use excel for anything we care about. I've already stopped using it for scientific work -- too dangerous to have things changed without me knowing. But when I import a list of student data and it takes someone's ID which is MAR09 and it says "oh, that isn't initials, it's a date!" I just don't know what to do.
I wish i will see it happens before i die.
So we are now changing the scientific names of genes because Microsoft can’t solve a more than 5 year old problem with excel.
@Alex: don't say things like that!
Now those team members, when they see this for the first time on the Christmas Party Whiner Roll will look at each other and as one pour some powdered testosterone on the backs of their hands and snort it with a flourish that would have done them proud in the '80's and whip their heads up as one and roar "RAWRRRRRRR Science b*tches kiss my *ss!!! Kowtow to your masters! All will bow to the mighty ones!"
It just isn't good to be their entertainment and THEN provide extra entertainment!