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

[aod2020]

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 another column that I can sort on as it is picking up some of the other text contained in the cell.

The data that I want to sort is the end value within the cell, is there a way of sorting the data based on the right hand value in the cell.  (I have formatted the data to align on the right hand side and it is this that I want to sort on).  See image.  Any help would be appreciated, thank you in advance.

[#2]

@aod2020 

Do all cells have "-20 " or "-DEC-20 " before the value on which you want to sort?

[#3 · Accepted solution]

@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.

[#4]

thank you, I'll try that. @Hans Vogelaar 

[#5]

@Hans Vogelaar  thank you so much for taking the time to answer my query.  It works a treat and has saved me a huge amount of time.