Sorting numbers saved as text across columns

[Dan in Germany]

Hi again, I have another complicated sorting problem which is actually a continuation of my last post, but with a new twist

old post:
http://www.mrexcel.com/forum/showthread.php?t=531311&highlight=sorting+text

The VBA code worded like a charm for me, but unbeknownst to me and my collegues at that time, I need to sort my results differently in order for the data to be accepted by another computer program.

I have a table with over 500 rows and each row is a location(saved as a number) and the columns are Minerals (also saved as a number) and thier % composition joined together with a "_" symbol (I changed the ":" symbol in the VBA Code from the last post because of formating problems).


example:
ColumnA; ColumnB; ColumnC; ColumnD;
Location; MineralCode_%Composition; etc.
350; 13_25.5; 22_14; 2_5.5

for Column B the mineral code is 13(Quarz) and composes 25.5% of the minerals at the location 350.

As in the last post, I do have to sort by % composition and I only can take the 14 most common minerals for input into the other computer program. What I need to do is also, in a second step, have those 14 minerals sorted by thier Mineral ID, which is to the left of the "_" Symbol.
I'm having a hard time finding a way to sort the minerals since they do not properly sort accross the columns using the sort command. I've tride using the CODE() command, but the ID's are more than one text character long. I also tried defining a custom list in which to sort, however, it also didn't sort correctly. The results of that sort is below.


Here is a real example of my data for location 226, columns are separated with a ";"

226; 1_18; 10_1; 107_1;108_12;109_6;15_2.6; 18_2.6;19_2.6;2_10;23_2.6;25_2.6; 3_0; 4_0; 9_9

Can anyone help me sort these rows?
Thanks in advance!

Dan

 

[Gerald Higgins]

In your sample data for 226, how do you want it to be sorted ?
Can you post the sorted version please ?

 

[Dan in Germany]

Thanks for your quick reply!

I would like the row to be sorted by the Mineral ID as follows:

226;1_18;2_10;3_0;4_0;9_9;10_1;15_2,6;18_2,6;19_2,6;23_2,6;25_2,6;107_1;108_12;109_6

In my table there would be about 500 rows, with more data comming at a later date.

Thanks!

Dan