Sorting Mix Numbers, Letters, and Decimals

  • Thread starter Thread starter shawnmsrm
  • Start date Start date
S

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
 
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
Click to expand...

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.
 
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
Click to expand...

Thank you very much !!!
 
Back
Top