Sorting Sizes
Posted by Teri Fields on August 21, 2001 4:58 PM
Mr. Excel,
I'm trying to sort an inventory list by a column that contains sizes i.e. 3.25x6, 10x12, 3x5, etc. Excel doesn't seem to put them in the right order either ascending or descending. Is there anyway I could do this without puting the sizes in different cells? I would greatly appreciate your help....Thank You!
Posted by Russell Hauf on August 21, 2001 5:26 PM
That's a good one. Since your fields are actually text, it will sort 10 before 3 because 1 is less than 3. I can think of 2 things you can do (on second thought, # 1 does not work, but I'll list it anyway):
1. Type any lengths less than 10 with a zero preceeding the number (ex: 03x05). However, this will still sort 03x05 after 03.25x6.
2. You could insert a column, have it read the first number of the size, then sort by that column. You could later hide this column.
So say your sizes are in column B, starting in B1. In cell A1, type the following formula:
=LEFT(B1,FIND("x",B1)-1)+0
Then drag the formula down as far as you have data.
This will give the number before the "x" in each "size" (so it will give 3.25 in the size "3.25x6"). Then you can sort your data by column A.
Hope this helps,
Russell
Not Mr. Excel
