# Thread: LEFT RIGHT combination help. Extracting MID data with many variables. Thanks:  2 Post #5359383 (1)Post #5359398 (1) Likes: 0

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

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  Reply With Quote

2. ## Re: LEFT RIGHT combination help. Extracting MID data with many variables.

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  Reply With Quote

3. ## Re: LEFT RIGHT combination help. Extracting MID data with many variables.

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

4. ## Re: LEFT RIGHT combination help. Extracting MID data with many variables.

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

5. ## Re: LEFT RIGHT combination help. Extracting MID data with many variables.

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

6. ## Re: LEFT RIGHT combination help. Extracting MID data with many variables. Originally Posted by 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
Worked like a charm! Ive never used REPT before. Trying to break it down! Very cooL!  Reply With Quote

7. ## Re: LEFT RIGHT combination help. Extracting MID data with many variables. Originally Posted by Kenneth Hobson 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!  Reply With Quote

8. ## Re: LEFT RIGHT combination help. Extracting MID data with many variables.

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```  Reply With Quote

9. ## Re: LEFT RIGHT combination help. Extracting MID data with many variables.

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```  Reply With Quote

## User Tag List

combine left and right, extract numbers string, extract text, find, mid 