software

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: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=507716On Thu, 2 Feb 2006 10:17:28 -0600, mphell0
gt; wrote:

gt;
gt;I have a list of streets that I want to put in ascending order. Some of
gt;the streets are named 1st, 2nd etc and others are just names. When I do
gt;a sort on the data it correctly puts the named streets in order but
gt;numbered streets appear out of order
gt;
gt;for example
gt;
gt;10th
gt;11th
gt;1st
gt;20th
gt;21st
gt;2nd
gt;30th
gt;31st
gt;3rd
gt;a
gt;b
gt;c
gt;
gt;Is there an easy way to sort so that it recognizes 1st and 2nd... in
gt;the correct order so that it would sort like this:
gt;
gt;1st
gt;2nd
gt;3rd
gt;10th
gt;11th
gt;etc.
gt;
gt;Any help would be appreciated.

Use a quot;helper columnquot; into which you extract only the numeric portion. Then
sort on that quot;helper columnquot;.

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 quot;sort anything that looks
like a number, as a numberquot;. Select that option. If that is not present, then
use this similar formula, which will converts the quot;textquot; number to a quot;realquot;
number:

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


Thanks Ron but I found an easy way to do it without using helper
columns.
Maybe this will help others in the future. In Toolsgt;Optionsgt;Custom
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 quot;optionsquot; that allows you to select a custom
list to
define the sort order. This worked perfectly.--
mphell0
------------------------------------------------------------------------
mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=507716On Thu, 2 Feb 2006 11:02:58 -0600, mphell0
gt; wrote:

gt;
gt;Thanks Ron but I found an easy way to do it without using helper
gt;columns.
gt;Maybe this will help others in the future. In Toolsgt;Optionsgt;Custom
gt;Lists
gt;you can create a list of values and how you want them sorted. I put
gt;in
gt;1st, 2nd, 3rd etc. into this list. Then when I went to do my sort, in
gt;the sort
gt;menu there is a tab called quot;optionsquot; that allows you to select a custom
gt;list to
gt;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