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.

 

[Ron Rosenfeld]

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

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

 

[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 !!!

 

[Ron Rosenfeld]

Thank you very much !!!

Glad to help. If there are more letters at the end, the concept would be the same (split the letters and numbers into two columns, etc), but the formula to separate would be different.