Sorting Issue

Hello,

I have been trying to solve an issue I have with how excel sorts my information so hopefully you gurus will be able to help me out. I have looked all over the web trying to find a solution and I can't seem to figure it out.

Attached is a spreadsheet that is a good example of what I am trying to accomplish. To give a little idea of why I need to sort in this way the numbers are part numbers in a warehouse and we are moving to a new warehouse so I need to be able to sort this list so that it is in the same order as the parts are in on my shelves so we now how to space the parts on the shelves.

If you go to row 56 you will see where there is an issue SBDS1464 is before SBDS737 where as on my shelf the order they are in is by the prefix first then by the root in ascending order so obviously 737 is way before 1464 and some of them also have a suffix and the way we handle those is for example on my shelf if we had SBDS737, SBDS737T and SBDS738 they would be in that order exactly. Then after we are all the way through SBDS prefix it goes to SBES and then by ascending order by the root number again and so on and so forth through all of the prefixes.

I am sorry if this doesn't make much sense if something is confusing let me know and I will do my best to clarify. Normally I would just do it manually as a normal sort gets it kind of close but for all of my lines the report will be thousands of lines long and it will take forever.

Thanks in advance the the help I appreciate it.

Kelly

You could use =LEFT(A1,4) and =RIGHT(A1,LEN(A1)-4) or just Text to Columns (fixed width) to split the code into two columns. Then sort on the "numeric part.

Would that give youwhat you want?

Regards, TMS

Also in my example all of the part numbers have a 4 letter prefix but in reality my prefixes vary in length.
Is there a way to if you have a string say abcd1235ef to tell excel to split that into abcd 1235 ef? The hard part is that the prefix root and suffix vary in length so it need to be able to split text number then text not a set number of characters.

You could use a UDF that expands the numeric part:

Please Login or Register  to view this content.

Then sort by the second column.

Please Login or Register  to view this content.

In Excel, press Alt-F11 to open up the VB Editor
Select Insert | Module
Copy and paste the code for the User Defined Function into the newly created module.

Back in Excel, in a cell in Row 1, type =PadNum(A1,4) as shown above. Copy down.

Sort on the new column.


Regards, TMS

Change the 4 to 5: =PadNum(A1,5) ... or 6 even, if you might have six digit numbers


Regards

Well, the credit goes to shg ... it was, after all, his UDF. I just helped tweak the fine detail.

But, thanks for the rep.