I am a big fan of Microsoft Excel. Between my day job and getting data ready to post on this website, I usually spend approximately 30 hours a week performing data cleaning, formatting, and analysis with Excel. It is hands-down the best spreadsheet program available. However, I do have one huge pet peeve with the program and that is how it handles numbers that have hyphens between them. The default in excel is to turn these numbers into a date. For instance, if you enter “4-10” into a cell, it is automatically turned into “10-Apr”.
According to Microsoft, this is a feature of the program where it is attempting to anticipate what you are trying to do. That’s really useful, unless you aren’t really trying to enter a date and actually want the hyphenated numbers.
There are actually three different solutions to this problem. Unfortunately, one of those solutions does not include going to the settings and unchecking a box to turn off this ‘feature’.
Stop Excel Auto Format to Date by Changing the Cell Format:
Probably the simplest way to stop this irritating problem is to simply change the formatting of the cell or cells before you enter the hyphenated numbers. Any cells that are pre-formatted as text will keep the numbers hyphenated as they are entered. You can do this by choosing the formatting drop down menu from the home tab, or by right clicking the cell, choosing format, then clicking on text under the number tab.
Another option is to enter an apostrophe in the cell before entering the hyphenated numbers. After pressing enter, the apostrophe disappears and the cell is automatically formatted as text.
Both of these solutions work perfectly if you are manually entering the data into the spreadsheet. A scenario that I find myself in much too often is that I have a text file with delimited data that I need to open in excel. If the data includes hyphenated numbers, they will be converted to dates automatically when the file opens. There is no conventional way to change the formatting of the cell(s) to text before the file opens.
Fortunately, there is one way to change the formatting of cells in this scenario. We can use the text import feature. It is found under the data tab, in the ‘get external data’ section. Just click on it and browse to your text file.
Choose the file that you want to import, then click open. Then the ‘text import wizard’ will open. Make sure ‘delimited’ is selected and click next.
On step two, you need to choose what character is dividing the columns in your data. In my case, it is commas. When you have made the correct selection, the preview window will show how your data will be divided like my example below. When you have the correct delimiter(s) selected, click next.
In step three, we will choose the format of each of the columns. For most of the data, the ‘general’ format works just fine. We need to be sure to click on the column where the hyphenated data resides and change the format to ‘text’. When done, click ‘finish’ to exit the wizard.
After exiting the text import wizard, Excel will ask you where you want to put the data. Usually cell ‘A1’ works just fine. Click ok and your data appears in the spreadsheet with hyphenated numbers that have not been converted to dates.