filtering only the maximal result from a column?

[galkim]

Hi there!
i'm conducting a retrospective medical research, and i got a chart with various numerical and categorical data.
I have several columns in which most cells contain multiple numerical or categorical results separated with semicolons, for example - "14.71; 15.48; 17.22; 22.72; 7.03"
OR "ASD closure; mechanical ventilation; TAPVR repair"
for numerical values - I want to filter out all the results except either the first or maxiamal result in each cell(havent decided yet ).
for categorical values - I want to omit cells that contain certain values, for example - all the ones that contain "ASD closure"

how can i do that?
example: https://imgur.com/a/IDZjpfV

 

[Legacy 386498]

Hi,

For the numerical data use:

=MAX(0+MID(SUBSTITUTE(A1, ";", REPT(" ",255)), 255*(ROW(INDIRECT("1:"&(1+LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))-1)+1,255))

Press Ctrl + Shift + Enter and not just Enter when copy\pasting this formula.

For the categorical data, if in cell B11 (on that image uploaded) you type ASD, in cell B12 put:

=IFERROR(SEARCH($D$4,A12),0)

0=No, 1=Yes

Then drag the formula down and filter all the 0.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, you will get many more potential helpers if you provide your sample data in a form that can be copied to a worksheet to test. My signtaure block below has help with that.

For the layout you have shown, a formula very similar to that provided by levedge but not requiring the Ctrl+Shift+Enter confirmation, for B2, copied down:
=AGGREGATE(14,6,MID(SUBSTITUTE(A2&REPT(";0",LEN(A2)),";",REPT(" ",255)),ROW(INDIRECT("1:"&LEN(A2)))*255-254,255)+0,1)

For the text list, in B12, copied down:
=IFERROR(INDEX(A$12:A$17,AGGREGATE(15,6,(ROW(A$12:A$17)-ROW(A$12)+1)/ISERROR(SEARCH("ASD Closure",A$12:A$17)),ROWS(B$12:B12))),"")