Extracting numerical values from a sentence

[chew3j]

Hi, I'm currently working on a way to extract a value from a clinical report, example "The left ventricular ejection fraction is calculated as 61% ( normal value > 60%)".

So far, I have been able to use a formula to extract the data (in this case 61%) by asking excel to find values after the words "calculated as" with the function


=LOOKUP(9.9E+307,--LEFT(MID(MID(C2,FIND("ated as ",C2)+2,999),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(C2,FIND("ated as ",C2)+2,999)&"0123456789")),999),ROW($1:$999)))

But I have found difficulties when the reports are not uniform, as some are reported with different precursor words.

Would anyone be able to help me with this? Or are there more efficient ways to extract a single numerical value from a sentence for Excel 2013?

 

[anglais428]

Will it always be a whole number percentage? or are decimals possible?

 

[chew3j]

It will always be reported as a whole number percentage

 

[anglais428]

Try:
=LEFT(RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1),3)

Tested with text in A1. When 61% in text, answer is 61%. When 1% in text, answer is 1%.

 

[anglais428]

Actually stick a TRIM on to avoid the trailing space:
=TRIM(LEFT(RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1),3))

 

[chew3j]

Thank you so much for your help anglais. I've tried both formulas out, and an issue occurs if there are other numerical values written down in the report that comes before the data I plan to extract. Is there anything I could do to fix this?

 

[anglais428]

Tricky. Basing off the first occurrence of the percentage sign then:
=TRIM(MID(A1,FIND("%",A1)-2,3))

 

[chew3j]

This is brilliant! I was thinking of approaching it this way, but couldn't get the formula to work. Yours is perfect!