Forum Discussion
Solved
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...
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.
Do all cells have "-20 " or "-DEC-20 " before the value on which you want to sort?