Sorting out variable length alphanumerical data

[axtryo]

Hi there,

New to the forum, and pretty much new to Excel. I have several workbooks (lists) containing variable length alphanumerical data I need to sort in a different way than Excel tries to entice me with. Example of the data sorted out by Excel:

SWC-0000
SWC-0007
SWC-00094
SWC-00126
SWC-0013
SWC-0015

I need the data sorted as follows:

SWC-0000
SWC-0007
SWC-0013
SWC-0015
SWC-00094
SWC-00126

Above example is just the beginning of a list containing a few thousands rows. All my lists starts with letters. Although some have 3, 4 or up to 6 letters. Then a dash, then numbers, some having 3, 4, 5 or 6 of them. What I'd like to have Excel to do, is sort all the ones with let's say 3 numbers first, then the ones with 4, etc.

Any help on this?

[Lemice]

Hello,

What you can do is create a helper column with =LEN(A1) assuming SWC-0000 is on Cell A1, then you can sort by the helper column first, THEN sort by the column with your data.

Hope this help.

[axtryo]

Hi Lemice,

I'm so glad this worked out beautifully! Thank you so much! I had spent HOURS trying to find out how to get this done with no success. Highly appreciated!

[FDibbins]

Hi and welcome to the forum

An alternate solution would be to a helper with this, copied down...
=MID(A1,SEARCH("-",A1,1)+1,99)*1

Then sort by the helper

[axtryo]

Hi FDibbins,

Thank you so much for your reply. I used the formula you gave me, but results were a bit unexpected. Below is a screenshot of the partial results, where you can see it still mixed items having 5 and 4 numbers:


Capture.JPG

Perhaps I'm missing something ... As I said, I'm not very literate when it comes to Excel. I used cell B1 to write the formula, then dragged it down until the end of the list. Then I sorted "smallest to largest". The image above shows the results. Did I miss anything? ...

[FDibbins]

Nope you are not missing anything. Your sample did not show...
SWC-0094
SWC-00094
and I did not cater for that, unless lemice's suggestion worked for you, I will try and fix mine?

[axtryo]

Hi again,

Well, I just came to realize, previous formula, using LEN, works fine with a list where all items start with the same set of letters, and only the numbers part change (which is helpful anyway). However, when I have a mix of items, things get more complicated and LEN will not render the results I need. Below is an abbreviated sample of a typical list I have to handle. First column shows how Excel sorts it out (sort A to Z), next column shows the way I'd like to have it. Third column shows how LEN sorts them out:

BMB-06067 BMB-1291 TRC-25
BMB-1291 BMB-06067 TRC-239
CHA-0162 CHA-0162 BMB-1291
CHA-98913 CHA-9929 CHA-0162
CHA-9929 CHA-98913 CHA-9929
FCR-01511 FCR-1090 FCR-1090
FCR-1090 FCR-01511 FEA-1015
FEA-01120 FEA-1015 XCR-5194
FEA-1015 FEA-01120 BMB-06067
TRC-25 TRC-25 CHA-98913
TRC-239 TRC-239 FCR-01511
WSS-21071 WSS-21071 FEA-01120
XCR-5194 XCR-5194 WSS-21071


I read somewhere that perhaps splitting the original column into 2, then sort it out, then putting them back together might work, but I'm not sure how to do that anyway.

I'm attaching one "real world" spreadsheet where items are mixed.

Any help will be highly appreciated.

[Lemice]

You can combine your criteria into one column with this formula

Please Login or Register  to view this content.

Then sort by that column alone will do the trick.
I have attached the sample file.

Edit:
In any case that even the number of letter before the dash is varying, you can use this formula instead

Please Login or Register  to view this content.

I think this one is better, you should try it out.

[axtryo]

Lemice,

I have no words to thank you for your time. This second formula worked just perfect. You have no idea how useful and important is this to me. Being a mostly new user of Excel, this little challenge had me stuck for a while. I'm so glad to see there's people out there willing to help. A true inspiration,

Thank you so much