13

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.

forums
  • 233

4 Answers4

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
Jan Doggen
  • 4,218
user351036
  • 41
  • 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
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
phuclv
  • 27,773
David Zemens
  • 716
  • 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 06:14
  • @forums "custom format @" - it's a symbol for Text format in VBA)) – Peter L. Feb 14 '13 at 08: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
  • Got it, I came across this. So that behavior was caused by excel 2010's bad guesswork. It assumes the DATE adjacent cell is also a Date. So I did the one in the link and presto, text stayed as text :yey:. I still wish MS stops adding nonsense to excel, or at least give as an easier option to remove it – forums Feb 14 '13 at 14:35
  • well when you think about it, it's not really "bad" guesswork. Most of the data input to Excel, by most users, anywhere, ever, is numeric data of some sort. So it makes sense that when a cell's value can be interpreted as numeric, that the application treats it as such until otherwise directed by the user. So it's really a good guess, 95% of the time. You happen to be using Excel somewhat unconventionally. Alternatively, you can just insert an apostrophe before you enter the value so it would be like `1-2 and Ecxel will always treat that as string/text and ignore the apostrophe. – David Zemens Feb 14 '13 at 14:49
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 (.).

Robert
  • 1
  • 1
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
G-Man Says 'Reinstate Monica'
  • 7,827
tired of excel
  • 1