sorting text & numbers

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?

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

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
>
> 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?
>
>
> --
> billjr
> ------------------------------------------------------------------------
> billjr's Profile: http://www.excelforum.com/member.php...o&userid=25927
> View this thread: http://www.excelforum.com/showthread...hreadid=393020

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[s]. 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.

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

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
>
> 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?
>
>
> --
> billjr
> ------------------------------------------------------------------------
> billjr's Profile: http://www.excelforum.com/member.php...o&userid=25927
> View this thread: http://www.excelforum.com/showthread...hreadid=393020