Excel 2007 Sorting alphanumeric

[asma410]

I found some posts related to my problem but the solution just doesn't work for me. I have a list of alphanumeric data that I need to sort.
original:
100A
1A
200A
2A
300A
9A

Sorted:
1A
2A
9A
100A
200A
300A

I tried using the method of using the TEXT formula in adjacent column [=Text (A1,"###")] & copy the values (paste special/values) on the original data, to make it text. Also tried Text to Column (under data tab) for sorting it correctly but nothing seems to work.
Not sure what I am doing wrong.

 

[Erick]

is the trailing text always just single character? If it is, then one UGLYway to do it is to trim the text away in another column, so that it is just left with the number part and then sort it using this column. So, assuming the sample data you posted starts from Cell A1, you can put this formula in column B: =Value(Left(A1,Len(A1)-1)). This formula assumes that you will always only have 1 trailing character. You can then sort your data using column B.

 

[jim may]

How should they look (show the order)?

 

[jim may]

also see,,,
http://support.microsoft.com/kb/214282

 

[asma410]

Sorry for the example in my original post. This is the correct format of the list I have:
21AA
21BA
2AW
2AX
2AY
2AZ
2BA
2CQ
2CR
2CS
2CT
2CU
2DA
2DB
2DC
2DD
31AA
31BA
31CA
32CA
32CB
32DA
32DC
51AA
51BA
51CA
51DA
51DC
52AA
52DC
61AA
61BA
61CC
61DA
61DC
71AA
71DA
71DC
72AA
72BB
72DC
73AA
73BB
73CA
74AA
74BB
9AA
9CC
9CD
9CE
9CF
9CG
9CH
9CJ

And I want to sort it like in ascending order (priority to numbers & then alphhabets) like this:
2AW
2AX
2AY
2AZ
2BA
2CQ
2CR
2CS
2CT
2CU
2DA
2DB
2DC
2DD
9AA
9CC
9CD
9CE
9CF
9CG
9CH
9CJ
21AA
21BA
31AA
31BA
31CA
32CA
32CB
32DA
32DC
51AA
51BA
51CA
51DA
51DC
52AA
52DC
61AA
61BA
61CC
61DA
61DC
71AA
71DA
71DC
72AA
72BB
72DC
73AA
73BB
73CA
74AA
74BB

Thanks for the replies.

 

[Andrew Poulsom]

You need to put a leading zero on the entries that are less than 4 characters. You can use this formula in B1 copied down:

=REPT(0,4-LEN(A1))&A1

and sort on that column.

 

[Norie]

Put this formula in a column next to the data and copy down

=--LEFT(D1,LEN(D1)-2)

Now sort the 2 columns by the column with the formula in it.

Note this will only work if the format is number then 2 letters.

 

[asma410]

Works like magic. Thank you so much for taking time to reply. I really appreciate it.