Help Sorting Alphanumeric Values

[ivanromero]

So I am trying to arrange a set of data in which there are certain codes that correspond to numeric values. Basically, I need to either find a way to assign text values, or to sort through a complex function that I am not sure how to compose.

The values in decending order are as follows:

16M
12G
11M
8GT
7M
6GT
5M
5000
4GT
3M
3000
2000
1000
500
250
175
50

But as you may have concluded, Excel sorts as follows:
(Probably how it sorts text)

1000
11M
12GT
16M
175
2000
250
3000
3M
4GT
50
500
5000
6GT
7M
8GT

If you have even a slight idea on how to complete, please let now. Anything helps!

Thanks in advance

 

[Special-K99]

Use a Helper column

in B1
=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)
Array formula, use Ctrl-Shift-Enter
and copy down

then sort by column B

Source: http://www.mrexcel.com/forum/excel-questions/362184-extracting-multiple-numbers-string.html