Forum Discussion
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 without Excel telling me that it is a number or a date and will be sorted differently. How can I do this?
I am using Office 2016. Windows 10.
- 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.
4 Replies
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?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.
