Help - Excel is sortin 10 before 2

This is what I am trying to create on a visual level. As you can see though it's not in the right order although the reason count for each party is displaying correctly. The 0 after the nine needs to go as well.

cheeseontoast101 

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]

Hi. To sort them by number values try this one.

Reasons is a name of the combination of Reason 1;...; Reason 10

=TRANSPOSE(SORT(VALUE(MID(UNIQUE(Reasons);8;100))))

Note: 8 in the formula is the start number of MID function. It means length of "Reason " +1 to catch the start of your numbers.

100 in the formula is the number of characters of MID function to after the start number. i thought that 100 characters are enough to catch numbers of reasons.

cheeseontoast101 

I put an excel file as well to be clearer for you. Do not forget to put a name before you try this formula or choose range instead of adding a name instead of "Reasons" in the formula.

cheeseontoast101 

That's because it is sorting text values not the numbers.

You may try something like this...

=TRANSPOSE(SORTBY(UNIQUE(reasons),SUBSTITUTE(reasons,"reason ","")*1))