Filtering within a single cell using excel-CodePudding

I have a spreadsheet with two columns (A, and B ).

Column A is a list of funds/companies and column B is funding rounds the companies have participated.

Within each cell there is multiples values separated by a semi-colon (;).

Example:

Depixus, Series A, 2021 ; Egle Therapeutics, Series A, 2022 ; Amolyt Pharma, Series B, 2020.

My question is there a way to filter the values within a cell. Currently all the values within the cells in column B are not in year order (2022, 2021, 2020 etc).

I need to get them in descending order:

Egle Therapeutics, Series A, 2022 ; Depixus, Series A, 2021 ; Amolyt Pharma, Series B, 2020)

in an efficient quick way that doesn't involve manually redoing them.

CodePudding user response：

With Office 365 and TEXTSPLIT()(Currently only to insider beta channel at the time of writing):

=LET(arr,TRANSPOSE(TRIM(TEXTSPLIT(A1,";"))),yr,RIGHT(arr,4),TEXTJOIN(" ; ",,SORTBY(arr,yr,-1)))

With current formula available in Office 365 for PC:

=LET(arr,TRANSPOSE(TRIM(FILTERXML("<a><b>"&SUBSTITUTE(A1,";","</b><b>")&"</b></a>","//b"))),yr,RIGHT(arr,4),TEXTJOIN(" ; ",,SORTBY(arr,yr,-1)))

CodePudding user response：

So, this is what I have tried,

Using Two O365 Insiders Beta Channel Functions -- TEXTSPLIT() & HSTACK() with Regular O365 Functions with LET() , TEXTJOIN() & SORT() Functions,

• Formula used in cell A5

=LET(a,TEXTSPLIT(SUBSTITUTE(A1,".",""),," ; "),b,RIGHT(a,4) 0,c,HSTACK(a,b),
TEXTJOIN(" ; ",,INDEX(SORT(c,2,-1),,1))&".")

Using O365 Functions --> LET(), TEXTJOIN() & SORT(), with Regular Excel Functions, FILTERXML() & CHOOSE()

• Formula used in cell A13

=LET(a,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,".","")," ; ","</b><b>")&"</b></a>","//b"),
b,RIGHT(a,4)/1,TEXTJOIN(" ; ",,INDEX(SORT(CHOOSE({1,2},a,b),2,-1),,1))&".")