Sort data based on the right hand value within a cell containing a string of data

Hi, I have copied data from another source into a column x multiple rows. The data within the column is of various widths and the text to column function will not satisfy to move the data to anot...

excel

HansVogelaar
Aug 27, 2021
aod2020

The following assumes that there will always be ...-nn (from a date in DD-MMM-YY format) before the value you want to sort on.

Let's say the data are in A2 and down.

In B2, enter the following formula:

=MID(RIGHT(SUBSTITUTE(A2, "-", REPT("-", 255)), 255), FIND(" ", RIGHT(SUBSTITUTE(A2, "-", REPT("-", 255)), 255))+1, 100)

Fill down.

Select B2, then sort.

P.S. This will treat all end values as text.