Sorting 1st, 2nd, 3rd...
 

I have a list of streets that I want to put in ascending order. Some of
the streets are named 1st, 2nd etc and others are just names. When I do
a sort on the data it correctly puts the named streets in order but
numbered streets appear out of order

for example

10th
11th
1st
20th
21st
2nd
30th
31st
3rd
a
b
c

Is there an easy way to sort so that it recognizes 1st and 2nd... in
the correct order so that it would sort like this:

1st
2nd
3rd
10th
11th
etc.

Any help would be appreciated.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=507716

Sorting 1st, 2nd, 3rd...
 

On Thu, 2 Feb 2006 10:17:28 -0600, mphell0
wrote:


I have a list of streets that I want to put in ascending order. Some of
the streets are named 1st, 2nd etc and others are just names. When I do
a sort on the data it correctly puts the named streets in order but
numbered streets appear out of order

for example

10th
11th
1st
20th
21st
2nd
30th
31st
3rd
a
b
c

Is there an easy way to sort so that it recognizes 1st and 2nd... in
the correct order so that it would sort like this:

1st
2nd
3rd
10th
11th
etc.

Any help would be appreciated.

Use a "helper column" into which you extract only the numeric portion. Then
sort on that "helper column".

Assuming a maximum of three digits in the number portion, one formula that will
extract the digits is:

=LEFT(A1,MATCH(FALSE,ISNUMBER(-LEFT(A1,{1,2,3})),0)-1)

(If there could be more than three, just change the array constant
accordingly).

In later versions of Excel, there is an option to "sort anything that looks
like a number, as a number". Select that option. If that is not present, then
use this similar formula, which will converts the "text" number to a "real"
number:

=IF(ISNUMBER(-LEFT(A1,1)),--LEFT(A1,MATCH(
FALSE,ISNUMBER(-LEFT(A1,{1,2,3})),0)-1),"")



--ron

Sorting 1st, 2nd, 3rd...
 

Thanks Ron but I found an easy way to do it without using helper
columns.
Maybe this will help others in the future. In ToolsOptionsCustom
Lists
you can create a list of values and how you want them sorted. I put
in
1st, 2nd, 3rd etc. into this list. Then when I went to do my sort, in
the sort
menu there is a tab called "options" that allows you to select a custom
list to
define the sort order. This worked perfectly.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=507716

Sorting 1st, 2nd, 3rd...
 

On Thu, 2 Feb 2006 11:02:58 -0600, mphell0
wrote:


Thanks Ron but I found an easy way to do it without using helper
columns.
Maybe this will help others in the future. In ToolsOptionsCustom
Lists
you can create a list of values and how you want them sorted. I put
in
1st, 2nd, 3rd etc. into this list. Then when I went to do my sort, in
the sort
menu there is a tab called "options" that allows you to select a custom
list to
define the sort order. This worked perfectly.

I'm glad that worked for you. Of course, you need to have all the items in the
list, or it may not sort properly. Also, I'm not sure (you should check it if
it may be an issue), if custom lists are stored with the workbook. That could
be an issue if you move the workbook to another machine.
--ron