Sorting Mix Numbers, Letters, and Decimals

[shawnmsrm]

I have Excel 2010 and need some help on sorting

This is the data I have

0.1G
0.3G
0.9G
100R
10Y
1G
30Y
3B
3G
90R
9Y
R


I need it to sort like the following

0.1G
0.3G
0.9G
1G
3B
3G
9Y
10Y
30Y
90R
100R
R

Can this be done?

Thank you.

Shawn

 

[Claus Busch]

Hi Shawn,

Am Thu, 15 Aug 2013 12:13:31 -0700 (PDT) schrieb (e-mail address removed):

0.1G
0.3G
0.9G
100R
10Y
1G
30Y
3B
3G
90R
9Y
R

I need it to sort like the following

0.1G
0.3G
0.9G
1G
3B
3G
9Y
10Y
30Y
90R
100R
R

your data in column A from A1 down. Then in a heloer column row 1:
=IF(CODE(LEFT(A1))>57,CODE(LOWER(LEFT(A1)))+1000000+(CODE(A1)<97)/2+IF(LEN(A1)=1,0,MID(A1,2,5)/1000),IF(CODE(RIGHT(A1,1))>64,LEFT(A1,LEN(A1)-1)*1000+CODE(RIGHT(LOWER(A1),1))+IF(CODE(RIGHT(A1,1))<97,0.5,0),A1*1000))
and copy down. Sort by the helper column ascending


Regards
Claus B.

 

[shawnmsrm]

Assuming there is always just one letter, and it is at the end, as you show above, then

With the values in A1:An

Enter the following:



B1: =--LEFT(A1,LEN(A1)-1)

C1: =RIGHT(A1,1)



Then select A1:Cn (or just a single cell in the table will do the same), then select the sort option on the data ribbon

Column B Smallest to Largest

Column C A to Z



Finally, you can hide, or delete columns B:C

Thank you very much !!!