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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Erick

Active Member
Joined
Feb 26, 2003
Messages
360
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.
 

asma410

New Member
Joined
Feb 1, 2005
Messages
35

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 1, 2005
Messages
35
Works like magic. Thank you so much for taking time to reply. I really appreciate it.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,773
Members
414,336
Latest member
Nicolas2465

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
Top