Hi! im really confused on how to sort these US patent numbers

[NoelGilling]

So i need to sort from min to max the values of the classes of more than 2000 patents, so the patent number is un A1 and the rest of the class numbers are in B, C, D.... and so on. the problem is that the format of the numbers are as follows: x as in number, a as in letter

xxx/xxx or xxx/xx or axa/xxxa or xxx/xxx.xx and so on, so you can imagine how tedious this thing is, i have no idea how to do it properly without having to double check all the data by hand. Can someone guide me ? perhaps its something very simple yet i cant see it for myself

[#2]

@NoelGilling Hi, it is not clear if you want to sort only a column or a combination of column here (and which columns should be included in the combination if so). Could you pick out some examples of the raw data and what are the desired sort result you wish to see so we could understand your logic before suggesting any solution.

[#3]

@hynguyen so this is what i need (ignore the colors, those are just to be clear) i want all the classes of patent numbers arranged from minor to max, like these examples, red means is not in the right position. So basically a want arrange horizontally the classes of each of the patent numbers, so when i do that with the sorting tool, the numbers dont arrange correctly as you can see in the example. 

[#4]

@NoelGilling I would like to offer you a solution that combines the SORT and TRANSPOSE functions. The SORT function, however, may not yet be available in your Excel version yet.

From the MS support pages on SORT:

This function is currently available to Microsoft 365 subscribers in Current Channel. It will be available to Microsoft 365 subscribers in Semi-Annual Enterprise Channel starting in July 2020.

Now, if your Excel does support the SORT function, you could enter on row 1 in the next empty column (in your case Q1):

=TRANSPOSE(SORT(TRANSPOSE(B1:P1)))

and copy it all the way down. Then you copy column Q:AE and paste it on top of itself as values. Now you can delete the original columns B:P. It will produce rows with sorted class codes with zeros at the end for empty cells within the B:P range of columns. So, patent numbers that do not have classes all the way to column P will have a bunch of zeros in the end. You can ignore then, get rid of them later or just format them to become invisible.

I also want to mention that you may need to clean-up the data first. For instance, on row 5 from column D onwards, the classes seem to have leading spaces before each code.

A class code like " 475/121" will be sorted before "180/370". If that is not your intention, use the TRIM function to get rid of these leading spaces, before you apply the SORT/TRANSPOSE formula above. If none of the class columns would have had empty cells, you could have incorporated TRIM into the formula like this:

=TRANSPOSE(SORT(TRIM(TRANSPOSE(B1:P1))))

But in your case it's going to put the empty cells first in the sort order. 

[#5]

thank you very much, i tried that, thanks for the info but im getting the same results @Riny_van_Eekelen 

you can look in the file attached. I dont know why some numbers are just not sorted correctly.

[#6]

@NoelGilling Thanks for attaching the file. That helped! It seems that the blank cells aren't really blank, and the behave odd when sorting. I wonder where the data comes from. Is it a download from somewhere or perhaps a csv file originally? And you do realise that we are sorting text here. Thus, "244/123" gets sorted before "244/79".

Furthermore, I noticed that you used TRANSPOSE/SORT/TRANSPOSE on the entire data array in one go. Hadn't thought about that, but it doesn't seem to work. You need to do it row-by-row and copy the formula down the column. Don't ask why.

The attached workbook arrived at sorted data in three steps. I took your raw data and replaced the "blanks"  with "z". Then, I sorted that table using TRANSPOSE/SORT/TRANSPOSE row-by-row. And in the last sheet I replaced "z" by blank. Not the most elegant way, but it works.

Also attached a file that achieved the same result using power query.