This is my example list: example file
As you can see I am sorting all unique elements in row B.
But the "Sprint 9" gets sorted as the highest value, not the lowest. I really have no clue how I can fix this. I found this solution and tried to adjust the formula to my needs, but couldn't get it to work as I expect it:
In Google Docs, how to make a column with several numbers in each cell sortable?
Best Answer
Key for the sort is the notation of the digits in the text (String #). These are sorted alphanumerically, meaning that
9
is higher than25
when sorting descendingly. This can be resolved by squeezing in a zero to all digits ranging from 1 to 9. See formula I constructed.Formula
Screenshot
Explained
The
MID
formula separates the 9th character from the string placed inA2:A
. If it matches a/
, then use theREPLACE
formula to insert a0
at position 8, by using zero as start position. If no match has been found, simply show the unaltered rangeA2:A
. All is wrapped inside anARRAYFORMULA
to take on ranges instead off one cell. The altered range is then fed to theUNIQUE
formula that will show only unique entries. This range is sorted by theSORT
formula, using the first column of the range (and the only one) and sorted ascendingly.