Discussion Re: Excel wiill not treat the entry 11-11-11 as a text entry. It keeps treating it like a date. in Excel
https://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2227649#M93898
The reply solves half my problem. But the entries have the little green triangle in the upper left corner.<BR /><BR />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.<BR /><BR />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.<BR /><BR />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?Mon, 22 Mar 2021 14:07:06 GMThankone2021-03-22T14:07:06ZExcel wiill not treat the entry 11-11-11 as a text entry. It keeps treating it like a date.
https://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2226552#M93835
<P>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?</P><P> </P><P>I am using Office 2016. Windows 10.</P>Sun, 21 Mar 2021 20:52:18 GMThttps://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2226552#M93835hankone2021-03-21T20:52:18ZRe: Excel wiill not treat the entry 11-11-11 as a text entry. It keeps treating it like a date.
https://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2226572#M93836
<P><LI-USER uid="1004203"></LI-USER> </P>
<P>Either format the cell as Text <EM>before</EM> entering the value, or prefix the value with an apostrophe.</P>
<P>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.</P>Sun, 21 Mar 2021 21:22:34 GMThttps://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2226572#M93836Hans Vogelaar2021-03-21T21:22:34ZRe: Excel wiill not treat the entry 11-11-11 as a text entry. It keeps treating it like a date.
https://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2227649#M93898
The reply solves half my problem. But the entries have the little green triangle in the upper left corner.<BR /><BR />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.<BR /><BR />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.<BR /><BR />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?Mon, 22 Mar 2021 14:07:06 GMThttps://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2227649#M93898hankone2021-03-22T14:07:06ZRe: Excel wiill not treat the entry 11-11-11 as a text entry. It keeps treating it like a date.
https://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2227662#M93900
<P><LI-USER uid="1004203"></LI-USER> </P>
<P>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.</P>
<P> </P>
<P>A date is a number in Excel, and Excel recognizes 11-11-11 as a possible date, hence the warning.</P>Mon, 22 Mar 2021 14:18:39 GMThttps://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2227662#M93900Hans Vogelaar2021-03-22T14:18:39ZRe: Excel wiill not treat the entry 11-11-11 as a text entry. It keeps treating it like a date.
https://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2228044#M93929
I have found an answer to my specific problem.<BR /><BR />I place 11-11-11 in a cell<BR />I format the cell as a Number then Custom then as Type 11-11-11.<BR />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.Mon, 22 Mar 2021 17:44:30 GMThttps://techcommunity.microsoft.com/t5/excel/excel-wiill-not-treat-the-entry-11-11-11-as-a-text-entry-it/m-p/2228044#M93929hankone2021-03-22T17:44:30Z