Forum Discussion
Help - Excel is sortin 10 before 2
Hi My formula is =transpose(sort(unique(reasons))) The reasons name range is just reason 1, reason 2, reason 3 etc. all the way to 10. Except it's not displaying order - Reason 10 is befor...
In beta version of Excel there is the TEXTAFTER function that removes the need for SEARCH/MID. Singling out the numeric part of the string allows it to be used as the sort criterion
= TRANSPOSE(UNIQUE(
SORTBY(reasons,VALUE(TEXTAFTER(reasons, " ")))
))An alternative approach might be to edit the text to start with "Reason 01", so aligning alphabetical and numerical sort.
[As an aside, I always find it strange that spreadsheet column headers are sorted in a manner that conforms neither to alphabetical nor numeric ordering]