CODEWITHSUNDEEP
google-sheets
BaconCatBug
BaconCatBug

Reputation: 195

Sort alphanumeric data in natural order in google sheets

I have a list of data I wish to sort in natural order, however it sorts in lexographical order. How can I sort in a natural order?

The data I have, which gets sorted as such:

A1
A10
A11
A12
A13
A14
A2
A3
A4
A5
A6
A7
A8
A9
B1
B2
B3
B4

The order I would like for it to be sorted in:

A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
A13
A14
B1
B2
B3
B4

Upvotes: 1

Views: 1127

Answers (2)

Luc Bloom
Luc Bloom

Reputation: 1253

If you have IDs that could potentially contain multiple numbers, like level8_text23, you may want to use this:

=SORT(A2:C99,REGEXREPLACE(A2:A99,"\d+$",""),1,TEXT(REGEXEXTRACT(A2:A99,"\d+$")*1,"0000000"),1)

Where A2:C99 is the range of the data you want to sort together. And A2:A99 is column with the IDs.

PS: "0000000" should be enough numbers for most use cases.

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9345

Suppose the original data as listed in your post resides in range A2:A19. Place the following formula in B2 of an otherwise open range B2:B (or in the Row-2 cell of any other open column where you want the sorted results):

=SORT(A2:A19,REGEXEXTRACT(A2:A19,"\D+"),1,TEXT(REGEXEXTRACT(A2:A19,"\d+")*1,"000"),1)

This formula assumes that the numeric portion of your strings is never longer than three digits. If it does exceed, just change 000 to include the number of zeros that match the max digits that may appear in the numeric portion of your strings.

Of course, you'll need to adjust the A2:A19 to match the actual range of the original data in your sheet.

Upvotes: 3

Related Questions