Hi Joshua,
These are sorting correctly.
When sorted:
The first six items each contain a three digit number in column A. These are interpreted as numbers. Numbers sort before text in an ascending sort, so these six come at the top.
All of the other rows contain a mix of alpha and numeric characters. These are sorted using alphabetical sorting rules. The sort proceeds one character at a time, starting at the left, grouping the list in order by first 'letter', then sorting each group by second 'letter', then sorting each subgroup by third 'letter', etc. Numeric character sort before A,
Sorted: Rows 14 and 15
009A6LP |
Warm Tender Love |
0100A6LP |
Holiday Cheer |
These sort in the order shown as "00" comes before "01"
If you want the 'numeric' prefix to sort in 'numeric order", then this part must have a consistent length in every row.
Sorted: rows 24 and 25:
0109A6LP |
Wanna Piece |
010A6LP |
We Just Click |
The first three characters of both strings are the same. at the fourth position, 9 comes 'alphabetically' before A, and determines the sort order."Item Number
All of your item numbers currently begin with a three digit or four digit prefix.
Several of the four digit prefixes begin with a digit other than zero, so shortening these to 3 digits will not work.
The easiest fix is to make every "item number" in column A start with four digits, followed by a letter (or a hyphen).
Here's the process:
- If row 1 is not already a Header Row, convert it to a Header row to remove it from the sort.
- Find and edit the six item numbers (327-332) consisting of only three digits with no trailing letters. Add at least one letter after the three digit number. (eg. 327a, 328a, etc.).
- Click on any cell in column B and press option-left arrow to insert a new column before column B (the new column will become column B, the current column B will become column C)
- Enter (or copy and paste) this formula into the new cell B2, then fill it down to the bottom of the column:
B2: IF(ISERROR(LEFT(A,4)+0),"0"&A,A)
- With all of column B still selected, press command-C to Copy, then go to the Edit menu and choose Paste Formula Results to replace the formulas with the calculated results.
- Sort ascending on column B. Check the result to see if the sort matches your expected result.
- If the sort is as expected, copy the "item number" label into the header row of column B, and delete column A.
Regards,
Barry
