problem sorting

[ty]

I can not get the following to sort correctly,
1
7
3
22 x 8
10 x 17
8 x 11
when I sort these they do as follows
1
3
7
10 x 17
22 x 8
8 x 11
it does fine with the single digits but as soon as there are others it sees
the 10 as a 1 and a 0 so it puts this one before the 8. Changing them to
text does not help. I believe it is in the format but I can't quite figure it
out.
Thanks,
ty

 

[Bassman62]

In your list, Excel is interpreting the singles as numbers and the rest as
text.
With cells formatted as Text re-enter your list manually then sort (A-Z), I
got:
1
10 x 17
22 x 8
3
7
8 x 11

 

[Gord Dibben]

You received a reply to a similar posting earlier today.

Basically you were told to break into two columns and sort on the one with
the numbers.

....................................................

You need a helper column. Assuming your list starts in A1, enter this in B1
by using ctrl+shift+enter, then fill down, then sort on column B:
=1*LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW($1:$10),1)),0)-1)

Bob Umlas
Excel MVP

..........................................................

Gord Dibben MS Excel MVP