Results 1 to 9 of 9

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

  1. #1
    New Member
    Join Date
    May 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by jordiejones; Oct 21st, 2019 at 10:41 AM.

  2. #2
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,932
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  3. #3
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default 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)

  4. #4
    New Member
    Join Date
    May 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  5. #5
    New Member
    Join Date
    May 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  6. #6
    New Member
    Join Date
    May 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by bosco_yip View Post
    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!

  7. #7
    New Member
    Join Date
    May 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Kenneth Hobson View Post
    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!

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    324
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    324
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •