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