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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,179
Office Version
  1. 365
Platform
  1. Windows
If words Give and by separate it:
=MID(F15,FIND("Give",F15)+5,FIND(" by",F15)-FIND("Give",F15)-5)
 

jordiejones

New Member
Joined
May 5, 2010
Messages
31

ADVERTISEMENT

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

jordiejones

New Member
Joined
May 5, 2010
Messages
31
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

New Member
Joined
May 5, 2010
Messages
31

ADVERTISEMENT

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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
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
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,788
Messages
5,655,307
Members
418,188
Latest member
Cas8423606

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
Top