Sort alphanumeric data in natural order in google sheets-CodePudding

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

CodePudding user response：

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.