Forum Discussion
Hella_de_Haas
Dec 25, 2019Copper Contributor
Buchstaben und Zahlenkombination in Excel sortieren
Liebe Community, ich habe in Spalte A verschiedene Studien aufgelistet, die ich gerne aufsteigend ordnen würde. Sie heißen jeweils s1 bis s7425. Ich möchte sie so ordnen, dass sie aufsteigend sin...
PeterBartholomew1
Dec 25, 2019Silver Contributor
Your formula seemed to suffer in translation but my first observation is that the codes you have specified are of the form Snnn and do not contain a hyphen, "-", so FIND will not return a value.
Sensible options might be
1) to reconsider your codes so that the numeric part has leading 0s to ensure it sorts correctly as text as well as numbers
2) to simply replace the leading "S" by "" to create numbers for sorting
= VALUE( REPLACE( A2, 1, 1, "" ) )
Because I am using Office365 insider, I can combine the conversion and sorting into a single formula
= SORTBY( codes, VALUE( REPLACE( codes, 1, 1, "" ) ) )
where 'codes' is a name given to the list starting A2.