yogi_Sort Data In A3:A in Ascending Order by #nnn

Google Spreadsheet   Post  #2072

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-10-2016

question by: Tackky:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/6SZF0GbgUh4;context-place=forum/docs

Sorting Order When Numbers are Treated as Text

Is there a way through a formula, an array, a script, anything... To fix the behavior of sorting text when numbers are involved?  I'll show you what I mean.  I have a comic book checklist and the sorting is basing the order on the first number in a text string like this...

Amazing Spider-Man (2015-) #1	Have
Amazing Spider-Man (2015-) #1.1	Want!
Amazing Spider-Man (2015-) #1.2	Want!
Amazing Spider-Man (2015-) #1.3	Want!
Amazing Spider-Man (2015-) #10	Have
Amazing Spider-Man (2015-) #11	Have
Amazing Spider-Man (2015-) #12	Have
Amazing Spider-Man (2015-) #13	Want!
Amazing Spider-Man (2015-) #14	Have
Amazing Spider-Man (2015-) #2	Have
Amazing Spider-Man (2015-) #3	Have
Amazing Spider-Man (2015-) #4	Have
Amazing Spider-Man (2015-) #5	Have
Amazing Spider-Man (2015-) #6	Have
Amazing Spider-Man (2015-) #7	Have
Amazing Spider-Man (2015-) #8	Have
Amazing Spider-Man (2015-) #9	Have

Do you see how all the double digit numbers starting with a 1 (like #10, #11, etc) all come before #2?  I'm sure it would do the same thing with triple digit numbers, etc.  It would be nice if it treated the numbers as numbers even within the text strings.

I know I could just go in and manually edit them all to include a zero first or something like that, but i have TONS of entries I'd have to do it for.  It would be much nicer and cleaner to have it sort through a formula of some kind.  I'm already using a formula for sorting while ignoring the opening "A", "An" or "The" using this...

=ARRAYFORMULA(IF(LEFT(A2:A,2)="A ",RIGHT(A2:A,LEN(A2:A)-2),IF(LEFT(A2:A,3)= "An ",RIGHT(A2:A,LEN(A2:A)-3),IF(LEFT(A2:A,4)="The ",RIGHT(A2:A,LEN(A2:A)-4),A2:A))))

If there was a way to adapt that (or add something) for the number issue, that would be amazing! Thank you for any help! :)