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

jordiejones

New Member
Joined
May 5, 2010
Messages
31
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
Code:
= 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
Code:
=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
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
Your instance worked phenomenal and caught an instance that I hadn't considered for MDI's! "2 puffs" You've a life saver!
 
Upvote 0
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!
 
Upvote 0
Hi
This an option with code
If you data in column A
Code:
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
 
Upvote 0
Well, I presume your data start at F15 down
So try
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,559
Messages
6,179,515
Members
452,921
Latest member
BBQKING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top