Soft number with alphabet

G

Guest

Guest
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Seems OK to me remember 2003 comes before 2003-A and that before 2003-C
hope this helps
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top