Sort not ordering product numbers as expected

Hi All

I am trying to sort some data into order, starting at S1000 through to S100145 but when I try it is not doing it the way I want it. They are product numbers. Codes starting S1000 - S1999 are one category, S2000 - S2999 are another and so on. But because it goes up to S10045 it is sorting S1000, S10000, S10005, S1001, S10010, S10015, S1002 and so on. Can anyone tell me what I need to do please.

Thank you in advance.

Beth

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. I have updated it for you this time because you are a new member. --6StringJazzer

Welcome to the Forum BethC01!

Your product numbers start with a letter. That means that they are text strings, not numbers. Text strings are sorted according to their initial characters, so the sort order you see is correct.

Here is a workaround. Add a column with this formula, assuming your part numbers are in column A:

Formula:

Please Login or Register  to view this content.

and copy down. This is the number extracted from your part number. You can use this column as your sort key.

If this is not clear or you can't get it to work then please attach a file. The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

I tried 6StringJazzers solution with my mock-up based on your initial post, and it worked perfectly. 6StringJazzers has a the better solution. I was not familiar with the =VALUE or =MID functions.

I've attached my initial mockup with 6StringJazzers formula in Column C.

VALUE converts a string to a number.

MID is a substring function where you can extract part of a string based on the starting character and number of characters. I used 99 to make it gets all the characters; it does not cause an error if this number is bigger than the available characters, it just uses them all.