Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Using unique identifiers that follow substring to extract substring

Latest post Sun, Oct 20 2013 8:21 PM by maxmiller. 2 replies.
  • Tue, Nov 20 2012 1:08 PM

    • DonFord81
    • Not Ranked
    • Joined on Tue, Nov 20 2012
    • Posts 1
    • Points 37

    Using unique identifiers that follow substring to extract substring

    I am working with drug data, and the data entry conventions are ... unconventional.  Multiple spacing, single spacing, hyphens, slashes ... I've got it all.

     

    My problem is that I can identify a small handful of patterns that follow the data I want to extract (drug strength), but nothing unique that comes before it.  To compound matters, there are some entries that contain spaces in the middle of the drug strength and there's no standard length.

     

    For instance, I might have "PROGESTERONE 175 MG SL TABLET" or "WP ESTRADIOL 1MG/0.1ML HRT CREAM" ... in these two cases, I want "175 MG" from the first and "1MG/0.1ML" from the second, and "MG" and "ML" are each followed by a trailing space, which means I can locate those particular substrings in the field.  Finding the start and stop points, however, is going to be a bear of a chore.

     

    Is there anything I can use to make this more doable?

    • Post Points: 37
  • Fri, Nov 23 2012 3:37 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 230
    • Points 3,462

    Re: Using unique identifiers that follow substring to extract substring

    Don

    I would start by manipulating each string as follows to ensure that numbers are space delimited

    • adding a space before every occurrence of MG, ML or any other character that might directly follow a number
    • replacing "/" with " / "
    • trim the resulting string

    I would then use the VBA Split function and examine each element of the resulting array to find the first numeric entry. Then it would be a matter of looking at the next few elements of the array to see which pattern was being followed.

    Good luck!

    Peter

    • Post Points: 5
  • Sun, Oct 20 2013 8:21 PM In reply to

    • maxmiller
    • Not Ranked
    • Joined on Mon, Oct 21 2013
    • Posts 1
    • Points 5

    Re: Using unique identifiers that follow substring to extract substring

    Did you try that using from different formula?

    "currently designing gadget cases for http://www.kekacase.com/design-your-own/phone-cases.html"

    • Post Points: 5
Page 1 of 1 (3 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.