Sorting alphabetically and numerically in Google Sheets or in Excel-CodePudding

I have these values:

ABC1
ABC2
ABC11
ABC4
ABC5
ABC15
ML1
ML2
ML10
ML3

I want to sort these values into numeric order. However in Google Sheets if you sort A-Z then you get

ABC1
ABC11
ABC15
ABC2
ABC4
ABC5
ML1
ML10
ML2
ML3

And not

ABC1
ABC2
ABC4
ABC5
ABC11
ABC15
ML1
ML2
ML3
ML10

Any ideas on how to get it sorted?

I want this sorted as I have a list of 2000 UK postcode sectors (which always start with letters and then numbers) that I want to be sorted alphabetically and then numerically

For example, the real life sorted data would look like

AB1
AB7
AB9
AB10
AB11
DT2
DT3
DT4
DT10
DT11
ML1
ML2
ML3
ML10
ML11

CodePudding user response：

Assuming:

Your values start with any alpha-chars sequence;
Your values end with any 1 digits.

Try:

Formula in B1:

=INDEX(QUERY({A1:A6,SPLIT(REGEXREPLACE(A1:A6,"\d ","|$0"),"|")},"Select Col1 Order By Col2, Col3"))

CodePudding user response：

Try-

=QUERY({A1:A10,INDEX(REGEXEXTRACT(A1:A10, "[A-Za-z] ")),INDEX(REGEXEXTRACT(A1:A10, "\d ")*1)},"select Col1 order by Col2, Col3")

CodePudding user response：

just sort it:

=SORT(A1:A, REGEXEXTRACT(A1:A, "\D "), 1, REGEXEXTRACT(A1:A, "\d ")*1, 1)