Forum Discussion
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 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.
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 Replies
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.
HansVogelaar 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.
thank you, I'll try that. HansVogelaar
Do all cells have "-20 " or "-DEC-20 " before the value on which you want to sort?
