I have a column with about 20,000 SKU's. When sorting the values are not ascending. They sort like this:
100-10-10-00000
1000-10-10-00000
235-52-22-00000
The one thousand number (before the first hyphen) is sorting just after the 100 value and before the 200 value. The thousand value is higher than the 200 value and should be after it. How can I sort just by the first few digits before the hyphen?
Mac mini, macOS 10.14
Hi Bill,
Because of the two or three hyphens in those 'numbers' they are not recognized as "numbers", and are sorted alphabetically as text values.
To sort the list by the numerical values that would be represented by the digits preceding the first hyphen, you'll need to set up an auxiliary column in which you extract those digits and convert them to Numbers (or modify them to sort correctly when sorted as text.
Here are two copies of the SKU values, copied from your post into a pair of tables, one using each of the sort schemes described above.
The unsorted tables. The formula shown is in cell B2 of the table on the left, and filled down to B5.
The same tables after each is sorted. The formula shown below this image creates the sort values for the alphabetical sort in column B of the right table (3-1). It is entered in B2 and filled down to B5.
Regards,
Barry
Hi Bill,
Here is one way
Formula in C2 (and fill down) =LEFT(B2,FIND("-",B2)−1)
Sort the table by that column.
Regards,
Ian.
Thanks for the follow up Barry.
I thought I had solved it without using VALUE because the results were left aligned (as numbers should be). Only when I tried to sort did I realise they were text.
Regards,
Ian.
Sorting question
