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)=
If there was a way to adapt that (or add something) for the number issue, that would be amazing! Thank you for any help! :)
