sorting text & numbers

[billjr]

I have a large list that I want sorted like this:

Ward 1
Ward 2 & 3
Ward 4
Ward 5
Wards 6-8
Ward 9
Ward 10

When I sort it, it says:
Ward 1
Ward 10
Ward 2 & 3
Ward 4
Ward 5
Wards 6-8
Ward 9

If I just take out the words Ward and Wards, it makes some of the
numbers into dates, and does something else to some other cells that
returns a big number. Anything I can do?

 

[jimhome]

If you have Ward 1 in Cell 1, you could create a separate column to use
it to sort. The formula to create your separate column could look like
this:

=""&RIGHT(A1,LEN(A1)-FIND(" ",A1))

Remember to sort everything as text (and not things that look like a
number as a number when you get prompted.)

Jim Shoenfelt

 

[Harlan Grove]

billjr wrote...

I have a large list that I want sorted like this:

Ward 1
Ward 2 & 3
Ward 4
Ward 5
Wards 6-8
Ward 9
Ward 10

....

You'll need another column to hold formulas that extract the first
number after Ward. If the list above were in A1:A7, enter the
following formula

B1 [array formula]:
=--MID(A1,FIND(" ",A1)+1,MATCH(FALSE,ISNUMBER(-MID(A1,
{1;2;3;4;5;6;7;8}+FIND(" ",A1),1)),0)-1)

and fill B1 down into B2:B7. Now sort A1:B7 on column B in ascending
order.

 

[jimhome]

If you have Ward 1 in Cell 1, you could create a separate column to use
it to sort. The formula to create your separate column could look like
this:

=""&RIGHT(A1,LEN(A1)-FIND(" ",A1))

Remember to sort everything as text (and not things that look like a
number as a number when you get prompted.)

Jim Shoenfelt