Hi. The SORTBY formula sorted the data in a column as follows:

GLUE, all purpose, 130 grams
GLUE, all purpose, 200 grams
GLUE, all purpose, 40 grams

I would like the sort result to be:

GLUE, all purpose, 40 grams
GLUE, all purpose, 130 grams
GLUE, all purpose, 200 grams



The usual help would be much appreciated. Thank you.

Attached Files

SortByAlphanumeric.xlsx (15.3 KB, 12 views)

You're going to struggle to do this, because the values in the Item column are treated as text. So the sorting looks at characters one at a time from the left until they're different, so 1 comes before 6 even if there are zeroes after the 1.
You could try to extract the numerical value into a different column but it won't be easy.
You could also try to edit the values so that there are leading zeroes, so instead of:
RING BINDER, plastic, 6mm x 1.2m
you have
RING BINDER, plastic, 06mm x 1.2m


and instead of:
STAPLE WIRE, heavy duty, binder type, 23/6
you edit it to:
STAPLE WIRE, heavy duty, binder type, 23/06


It'll get difficult when you have the likes of:
1 1/2"
and

1 3/8"
because the 3/8ths is smaller than the 1/2 numerically, but not alphabetically.

Your are right, it is a struggle. I tried adding helper columns for size and measurement (mm, ml, g..) but still 23/13 came before 23/6 or 23/06 so the helper columns were discarded. I guess we will have to live with the not so perfect sort results.

If I edited to 23/06 on the List sheet it came out in the correct order on ListSort sheet!

Ooops. My mistake. Yes, placing a zero before the single digits would sort the data the way they should be sorted. Thank you.