Stop Auto Convert of Value to Date

[RammsteinNicCage]

I know this question gets asked a lot, but I haven't found a solution that fits my needs yet - I hope one is out there.

I have a value, 7440-09-7, that excel converts to a date. I do not want it as a date, I want it to display exactly as I typed it in. I can convert it to text or a number with zero decimal places and get that, but then I cannot numerically sort that column. I get a message that says:

The following sort key may not sort as expected because it contains some numbers formatted as text: Column B
What would you like to do?
* Sort Anything that looks like a number, as a number
* Sort numbers and numbers stored as text separately

The second option definitely is not what I want. The first option sounds like it would work, but it doesn't - it kicks that number to the top of the list even though there are other numbers greater or less than it.

Any ideas?

 

[lenze]

Custom format you cells like this
####-##-#
They will then display as you require, but still remain numeric!!
lenze

 

[RammsteinNicCage]

I'm still getting the same message and it still kicks the number up to the top....

 

[lenze]

With that Format, don't enter the "-"s in the cell. The format will insert them!!
lenze

 

[RammsteinNicCage]

Ok, now it will sort without the message, but it still kicks it to the top. :\

 

[lenze]

What are some of the other numbers??
lenze

 

[RammsteinNicCage]

Here are the numbers around it:

7439-96-5
7439-97-6
7440-02-0
7440-09-7
7782-49-2
7440-22-4

 

[lenze]

With your data I get a good sort
Ascending
<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=165 border=0><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 124pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=165 height=20>7439-49-2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7439-96-5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7440-02-0</TD></TR>
<TR style="HEIGHT: 15pt" height=20><TD class=xl63 id=td_post_2699828 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7440-09-7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7440-22-4</TD></TR>
<TR style="HEIGHT: 15pt" height=20><TD class=xl63 id=td_post_2699828 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7782-49-2</TD></TR>

</TBODY></TABLE>

Descending
<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=165 border=0><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 124pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=165 height=20>7782-49-2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7440-22-4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7440-09-7</TD></TR>
<TR style="HEIGHT: 15pt" height=20><TD class=xl65 id=td_post_2699828 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7440-02-0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7439-96-5</TD></TR>
<TR style="HEIGHT: 15pt" height=20><TD class=xl65 id=td_post_2699828 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7439-49-2</TD></TR>

</TBODY></TABLE>

lenze

 

[RammsteinNicCage]

Hmm... could it be that I'm on Excel 2003?? Or maybe because all of my other numbers are entered the regular way without the ####-##-# formatting?

 

[RammsteinNicCage]

Ah, looks like it was because I have to convert everything. Thanks a lot, MS! Boo.

At least my ##-##-# numbers also seem to sort correctly with the ####-##-# numbers.

Thanks for the help, Lenze!