Forum Discussion
Solved
Excel wiill not treat the entry 11-11-11 as a text entry. It keeps treating it like a date.
I wish to add the entry 11-11-11 as a title for a movie. I then wish to be able to enter this in Excel without it being changed to a date or treated as a number. And then be able to sort this with...
- I have found an answer to my specific problem.
I place 11-11-11 in a cell
I format the cell as a Number then Custom then as Type 11-11-11.
This permits 11-11-11 to be shown without the green triangle and more importantly allows the column this cell is in to be sorted without generating a Sort error.
Either format the cell as Text before entering the value, or prefix the value with an apostrophe.
When you enter '11-11-11, the apostrophe tells Excel to treat the value as text. The apostrophe will be displayed in the formula bar, but not in the cell itself.
The reply solves half my problem. But the entries have the little green triangle in the upper left corner.
When I try to sort the data containing 11-11-11, I get the Sort warning that I have some numbers stored as text. Since I do not have any numbers stored as text (11-11-11 is not a number), I do not need this warning and shouldn't get it.
If I put 0 11-11-11 in the cell, Excel recognizes this as not a number and lets me sort it without the Sort warning.
How to I get Excel to recognize that 11-11-11 is not a number when I do a Sort containing this in the cell?
When I try to sort the data containing 11-11-11, I get the Sort warning that I have some numbers stored as text. Since I do not have any numbers stored as text (11-11-11 is not a number), I do not need this warning and shouldn't get it.
If I put 0 11-11-11 in the cell, Excel recognizes this as not a number and lets me sort it without the Sort warning.
How to I get Excel to recognize that 11-11-11 is not a number when I do a Sort containing this in the cell?
I find those green error triangles irritating. I have turned them off by clearing all the check boxes in the Error Checking section of File > Options > Formulas.
A date is a number in Excel, and Excel recognizes 11-11-11 as a possible date, hence the warning.
- I have found an answer to my specific problem.
I place 11-11-11 in a cell
I format the cell as a Number then Custom then as Type 11-11-11.
This permits 11-11-11 to be shown without the green triangle and more importantly allows the column this cell is in to be sorted without generating a Sort error.