Soft number with alphabet

Hi Excellers,

I copy the following number from WORD to EXCEL. I try to soft its with ascending order. But its seem to be won't work for those number with alphabet. How can I ratify its. Thank you.

For example:
2001
2002
2004
2005
2007
2008
2010
2003-L
2006-O
2009-HK

[JohnG]

Seems OK to me remember 2003 comes before 2003-A and that before 2003-C
hope this helps

[IML]

First, sort your list (this will sort 2000-a,2000-b, etc properly.) add a temporary column with the formula
=IF(ISNUMBER(A1),A1,LEFT(A1,FIND("-",A1)-1)+0)
and copy it down.

Sort both columns by this new column. Then you can delete the new one if you like.

Good luck.

On 2002-02-22 05:35, Anonymous wrote:
Hi Excellers,

I copy the following number from WORD to EXCEL. I try to soft its with ascending order. But its seem to be won't work for those number with alphabet. How can I ratify its. Thank you.

For example:
2001
2002
2004
2005
2007
2008
2010
2003-L
2006-O
2009-HK

[George J]

Unless you mean you want the screen to show:
2001
2002
2003-L
2004
2005
2006-O
2007
2008
2009-HK
2010

If this is the case, put the following formula in the next column and copy it down for the length of your entries. Then select the 2 columns and sort by the one with the formula.

=LEFT(A1,4)

[mark]

Hi JohnG. IML, George J anfd others:

Thank you for your advice.

I try your formula its seem to be won't work. The number still show in the following order:
2001
2002
2004
2005
2007
2008
2010
2003-L
2006-O
2009-HK

I want the number show in the following order:

2001
2002
2003-L
2004
2005
2006-O
2007
2008
2009-HK
2010

I doubt whether is FORMAT problem. (Since my data is directly copy from MS WORD).

Mark

[dognz]

Put your list in column A and put the following formula in the correspondin cells column B:

=LEFT(A1,4)&MID(A1,5,3)

Then sort by Column B - you will get the result you are after.

[mark]

Thanks for the suggestion.