Wednesday, August 10, 2016

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

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! :)