How to disable Excel number autoformat

This problem is simple to correct but is very very annoying
what I'm doing is to type a hour range, eg. (0-1),(1-2),(2-3) so on per cell row

DATE --- HOUR
1/1/2013 0-1
1/1/2013 1-2
1/1/2013 2-3
1/1/2013 3-4

Excel stupidly keeps formatting 1-2 to a date 2-Jan
to make matters worse is when I change it to a number format
it gives 41276

I also cannot find the AutoFormat option in excel 2010, I am aware of the Format button in home but there is no autoformat there.

4 Answers

Before you enter any values in the column/cells, change the cell format to Text.

Or, put this in the Workbook code module to force this formatting on all sheets, each time the workbook is opened.

Private Sub Workbook_Open()
Dim sh As Worksheet
For Each sh In Me.Sheets sh.Cells.NumberFormat = "@"
Next
End Sub

1. cut and paste everything into note_pad
2. delete what ever left in xl
3. Select all needed cells, columns row to text format
4. re-paste from note_pad
5. if you need later you can change to date, time formats

1. Select all your '.csv' data and copy to notepad
2. Set all cells in new worksheet in Excel to text
3. Paste all your data from Notepad as text
4. Copy the "affected" column back to Notepad
5. Change all your . to , (e.g. 10.00 to 10,00)
6. Open new Excel book - IMPORTANT
7. Copy your column from Notepad to this new Excel book
8. Copy and paste the column back to your original Excel book

The only drawback is that you have your numbers now with (,) instead of (.).

Another way is:

1. save sheet as CSV
2. rename it to .txt
3. reopen it in Excel
4. import module is invoked
5. switch from general to text format for each numeric column