LEFT RIGHT combination help. Extracting MID data with many variables.

[jordiejones]

So I am dealing with a big project, trying to tear apart written MD orders for reporting. I have hundreds of patients and pull a very long dense report and figured out some of the keys to make it bite size. Take for example the following:

DiphenhydrAMINE HCl Tablet Give 50 mg by mouth at bedtime for sleep disturbance for 45 Days

right now I am trying to strip out the "50 mg". I've found that I can account for order with "mcg" by using the following formula

= LEFT('BETA Routine Meds Entry'!F15,(FIND("mg",'BETA Routine Meds Entry'!F15,1)+2))

but it pulls all the text before the 50 mg.
Then I've found

=RIGHT('BETA Routine Meds Entry'!F15,LEN('BETA Routine Meds Entry'!F15) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, 'BETA Routine Meds Entry'!F15&"0123456789")) +1)

will cut to the dosage whether it is "2000 mg" or "1mg" or "0.5 mcg". My problem is that I can't figure out a code to give me just the "50 mg" or "0.5 mcg" for example (NOTHING ELSE).

Any help would greatly be appreciated!

- Jordan

 

[bosco_yip]

Try this one :

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT('BETA Routine Meds Entry'!F15,LOOKUP(9^9,FIND({"mg","mcg"},'BETA Routine Meds Entry'!F15))+2))," ",REPT(" ",99)),200))

Regards

 

[Kenneth Hobson]

If words Give and by separate it:
=MID(F15,FIND("Give",F15)+5,FIND(" by",F15)-FIND("Give",F15)-5)

 

[jordiejones]

Worked like a charm! Ive never used REPT before. Trying to break it down! Very cooL!

 

[jordiejones]

Your instance worked phenomenal and caught an instance that I hadn't considered for MDI's! "2 puffs" You've a life saver!

 

[jordiejones]

Try this one :

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT('BETA Routine Meds Entry'!F15,LOOKUP(9^9,FIND({"mg","mcg"},'BETA Routine Meds Entry'!F15))+2))," ",REPT(" ",99)),200))

Regards

Worked like a charm! Ive never used REPT before. Trying to break it down! Very cooL!

 

[jordiejones]

If words Give and by separate it:
=MID(F15,FIND("Give",F15)+5,FIND(" by",F15)-FIND("Give",F15)-5)

Your instance worked phenomenal and caught an instance that I hadn't considered for MDI's! "2 puffs" You've a life saver!

 

[mohadin]

Hi
This an option with code
If you data in column A

Sub test()
    Dim a, b As Variant
    a = Application.Transpose(Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1))
    ReDim b(1 To UBound(a))
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d+.[a-z]+"
        For i = 1 To UBound(a)
        Set m = .Execute(a(i))
            b(i) = m(0)
        Next
    End With
    Cells(1, 2).Resize(UBound(b)) = b
End Sub

 

[mohadin]

Well, I presume your data start at F15 down
So try

Sub test()
    Dim a, b As Variant
    lr = Cells(Rows.Count, 6).End(xlUp).Row
    a = Application.Transpose(Cells(15, 6).Resize(Cells(Rows.Count, 6).End(xlUp).Row - 14))
    ReDim b(1 To UBound(a))
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d+.[a-z]+"
        For i = 1 To UBound(a)
        Set m = .Execute(a(i))
            b(i) = m(0)
        Next
    End With
    Cells(15, 6).Offset(, 1).Resize(UBound(b)) = b
End Sub