sort numbers in excel10

Question

this is a sample of my movie list as you can see 61 is at the top and 8mm at the bottom please explain how i can have 1 at the top and 3000 at bottom

61
.45 2006 dvd
10 1979 dvd
10 items or less 2006 dvd
10 Years 2011 bluray
10,000 bc dvd     
10.5 2004 dvd
100 girls 2000 dvd
100 rifles 1969 dvd
11:14 2003 dvd
12 angry men b-w 1957 dvd
12 Dogs of Christmas 2012 dvd
12 rounds 2009 dvd
127 hours 2010 dvd
13 going on 30 2004 dvd
1313 Cougar Cult 2012 bluray
16 blocks 2006 dvd
1941 1979 dvd
2 days in the valley  bluray   
2 fast 2 furious 2003 dvd          
20,000 leagues under the sea 1916 dvd
20,000 leagues under the sea 1954 a    
20,000 leagues under the sea 1954 b    
20,000 leagues under the sea richard crenna 1997 dvd
2001 space odyssey 1968 dvd
2010 the year we make contact 1984 dvd
2012 doomsday 2009 dvd
2012 Doomsday 2009 bluray
2012 supernova 2010 dvd
21 2008 dvd
21 grams 2003 dvd
24 season 1 ds1 dvd 2001-2010 dvd
24 season 1 ds2 dvd 
24 season 1 ds3 dvd
24 season 1 ds4 dvd
24 season 1 ds5 dvd
24 season 1 ds6 dvd
24 season2 dsk1 2001-2010 dvd
24 season2 dsk2
24 season2 dsk3
24 season2 dsk4
24 season2 dsk5
24 season2 dsk6
24 season2 dsk7
24 season3 dsk1
24 season3 dsk2
24 season3 dsk3
24 season3 dsk4
24 season3 dsk5
24 season3 dsk6
24 season3 dsk7
24 season4 dsk1
24 season4 dsk2
24 season4 dsk3
24 season4 dsk4
24 season4 dsk5
24 season4 dsk6
24 season4 dsk7
247 Degrees Fahrenheit 2011 bluray
28 days 2000 dvd
28 days later 2002 dvd
29 palms 2002 dvd
3 ninjas 1992 dvd 
3 westerns bat masterson annie oakley cisco kid dvd     
30,000 leagues under the sea 2007 dvd
300 2007 dvd
3000 miles to graceland a 2001 dvd
3000 miles to graceland b dvd
310 to yuma 2007 dvd
388 arletta avenue 2012 dvd
4.44 last day on earth 2011 bluray
40 days and 40 nights 2002 dvd
48 hours 1982 dvd
48 hours another 1990 dvd
5 children and it 2004 dvd
54 1998 dvd
6 bullets 2012 bluray
7 seconds 2005 dvd
8 seconds 1994 dvd
88 minutes 2007 dvd
8mm 1999 dvd
8mm2 2005 dvd

thank you in advance for your help

Answer 1

At a glance it seems you have several types of fields for 1 movie, e.g. 3000 miles to graceland a 2001 dvd has:

• 3000 miles to graceland - looks like title
• a 2001 - disk index & year
• dvd - disk (media) type

Perhaps some of them are combined, but that does not matter much here. Thing that really matters - Excel treats all your strings as STRING, that is, names with leading numbers are sorted as strings, that's why 1 abc, 2 xxx and 100 www will be sorted as 1-100-2. To achieve what you want you need to separate your string into 2 parts - 1st is leading number, 2nd is the rest. Assuming you put the above list in column A starting A1, do the following:

1. B1: =SUBSTITUTE(SUBSTITUTE(LEFT(A1,IFERROR(SEARCH(" ",A1),LEN(A1))),",",""),".","") - this will extract leading number and replace any separators for decimals (.45) or thousands (20,000). Autofill formula down your list.
2. Insert a new line ABOVE the list and formula, name columns e.g. "Strings" (A) and "Numbers" (B).
3. Add filters to columns, sort Numbers column A to Z.

As a result, your list will be properly sorted - strings with numbers will be going first, with 1-2-100 order - as desired) All the rest which is NOT starting with numbers will be sorted OK as well.

I'm pretty sure you'll find some movies with exception titles which still be sorted wrong, but that straightforward solution will do about 95% of routine job, and the rest is OK to polish manually. Moreover, you may add more ``=SUBSTITUTE(` "wrappers" to handle unwanted symbols - I suppose that's an easy part.

Sample file is here: https://www.dropbox.com/s/ze76cf5btdxnp0k/MoviesSorting.xlsx

Hope that helps)

Answer 2

By default Excel does not sort the way we would expect it to. Here is a great Knowledge Base article at Microsoft which should be helpful to you - How to correctly sort alphanumeric data in Excel.

When you sort a column that contains alphanumeric characters, the sort may return unexpected results. Excel sorts the values left to right, character by character. For example, if a cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11."

Why 61 is at the top may be because there is a space before the number.