Ask a new question

How to sort A-Z sort the letters and numbers A1,A2,A10,B1 etc

Hi

I have the following code to sort A-Z,

rivate Sub CommandButton1_Click()

Columns("A:A").Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

   
End Sub

 

However I would like to be able to sort the following codes(the actual list is longer with other letters etc)  A-Z, If I add say A13 - new code and and then A-Z I get A1,A10,A11 etc

A1 - No F731 / One F731
A2 - F731 No / Incorrect NSN
A3 - F731 No / Incorrect Pt No
A4 - F731 No / Incorrect #
A5 - F731 Incorrect MC
A6 - F731 No Fault Description
A7 - F731 No LCN, FC, Hrs, WHF
A8 - F731 No SNOW
A9 - F731 No NEO data statement
A10 - F731 Not Signed
A11 - F731 No ESS Signature
A12 - F731 No Batch details
B1 - No MJDI p/w
B2 - MJDI Incorrect NSN
B3 - MJDI Incorrect #
B4 - MJDI Incorrect MC
C1 - No ERC
C2 - ERC Incorrect Hrs

 

I hope this makes sense, Thanks

Answer
Answer

Because column A contains text values, it is sorted alphanumerically. 10 comes before 2 alphanumerically.

To remedy this, insert two helper columns in B and C.

In B2, enter the formula =LEFT(A2,1)

In C2, enter the formula =1*MID(A2,2,FIND(" ",A2)-2)

Select B2:C2 and fill down as far as needed.

You can now sort on columns B and C:

 

Range("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, _
    Key2:=Range("C1"), Order2:=xlAscending, Header:=xlYes

---
Best wishes, HansV
https://www.eileenslounge.com

5 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated December 28, 2024 Views 18,615 Applies to: