Excel 2007 Sorting alphanumeric

asma410

New Member
Joined
Feb 1, 2005
Messages
35
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Works like magic. Thank you so much for taking time to reply. I really appreciate it.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top