CODEWITHSUNDEEP
sortinggoogle-sheetsgoogle-sheets-formula
goose84
goose84

Reputation: 292

Sorting alphabetically and numerically in Google Sheets or in Excel

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

Upvotes: 1

Views: 737

Answers (3)

player0
player0

Reputation: 1

just sort it:

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

enter image description here

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36840

Try-

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

enter image description here

Upvotes: 0

JvdV
JvdV

Reputation: 75840

Assuming:

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

Try:

enter image description here

Formula in B1:

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

Upvotes: 1

Related Questions