Google-sheets – Applying a custom sort to Google Sheets string column

You can add MATCH to your SORT for a custom order list.

The formula in D5 is,

=ArrayFormula(sort(A$2:A$99,match(left($B$2:$B$99,3),{"sop";"con";"ten";"bas"},0),1))

Copy to E5. Without speed testing the calculation cycles it's debatable whether using the Left function to peel off the first three characters is seriously detrimental but it certainly makes creating the constant array for the Match range lookup easier to create and maintain. Be careful that you do not duplicate the first three characters if you expand the list.

Extracting the date with regex, and converting the result with datevalue isn't that much work:

=datevalue(regexreplace(A2, "^\w* (\d+) (\w+) (\d+).*", "$1-$2-$3"))

Then sort by this new column.

Explanation: the three captured groups in the regex are the year, month, and day. The whole string gets replaced by year-month-day, which isn't quite ISO standard (month is in text) but is clear enough for datevalue to convert correctly.