Extract specific text within a text string

Hi,

I have a list that contains dosages of drugs however the dosage appears in different parts of the text string. For instance:

Drug A 50mg twice a day
50mg Drug B Once a day
50 mg Drug C twice a day
Twice a day Drug D 50mg

In the column next to the list, I would like to extract just the drug dosage (wherever it appears in the text string). I've been play around with the left, right and search functions but can't get it to work without pulling in other parts of the text (ideally one formula that looks for all the combinations would be great).

list 2.jpg

Thanks in advance

This works for the small sample that you shared.

B2 =TRIM(MID(A2,MAX(SEARCH("mg",A2)-3,1),5))

That's perfect, thanks so much. What if it was a single number like 2mg or 2 mg (with a space in the middle)?

This is often the case with requests like this.

The best way to go about building a formula that will work for all cases would be to make your sample more representative of your actual data.

That being said, you can try this monster:

=LEFT(SUBSTITUTE(A2,LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1),""),SEARCH("mg",SUBSTITUTE(A2,LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1),""))+1)

Wow that is a monster, brilliant!
Thanks so much

You're welcome. Thanks for the rep!