11

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.

migrated from stackoverflow.com Feb 14 '13 at 10:02

This question came from our site for professional and enthusiast programmers.

  • 1
    Select cells, right-click on them and change cell format from General to Text – Mikhail Vladimirov Feb 14 '13 at 6:05
  • Hour range means 1AM -2AM? – user187182 Feb 14 '13 at 6:06
  • tried changing it to text but it still keeps on insisting the date. And also using military format 1-24 so no am/pm there – forums Feb 14 '13 at 6:07
  • 2
    You have to change it to text before you enter values. See my answer. – David Zemens Feb 14 '13 at 6:09
  • 3
    Simplest way is to type '1-2 - the ' tells Excel to treat the entry as text – chris neilsen Feb 14 '13 at 7:07
3

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
  • I think using the custom format @ solved it for me, the Text doesn't seem to work w/ch is weird. Also do you know of a way to disable/enable autoformatting function of excel? There used to be one in xl2003 – forums Feb 14 '13 at 6:14
  • @forums "custom format @" - it's a symbol for Text format in VBA)) – Peter L. Feb 14 '13 at 8:09
  • I tried googling and knew about changing it to text but like my comment above, it keeps being reverted to date. Don't know why @ worked fine. Thanks for the ms forum link too, learned that autoformatting cannot be simply turned off :sigh: – forums Feb 14 '13 at 14:01
  • I feel your pain with excel. My boss just wanted to do everything in excel when I was insisting to use access :sigh again:. Also do you think the reason it reverts to date is because the adjacent cell is in date format? – forums Feb 14 '13 at 14:10
  • can't say for sure about the revert issue, but Excel (and other MS products, for that matter) have been known to be a bit user-unfriendly when trying to "intelligently" do things, like formatting (Excel), indenting (Word), etc. – David Zemens Feb 14 '13 at 14:22
4
  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
  • Not happy about having to take the extra step of copy and pasting into notepad then re-pasting into excel. It's the only way my "dashed" entries don't convert to a date. The dashed entries are coming from a java table from an outside source. No other work around has stopped the conversion other than going through the notepad step. – CSmith Jul 30 '17 at 20:28
  • Another way I use is to put ="1-2" as I have needed the same range info for stuff before - downside is it does not work in calculations... – Solar Mike Jul 30 '17 at 20:39
0
  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 (.).

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.