+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : If a cell contains this part text, or this part text, etc.., return this value, or nothing

  1. #1
    Registered User
    Join Date
    04-06-2012
    Location
    Egypt
    MS-Off Ver
    Excel 2007
    Posts
    23

    If a cell contains this part text, or this part text, etc.., return this value, or nothing

    Hi,

    I have a column of texts of diagnosis description which looks like this:

    upper respiratory tract infection
    respiratory tract infection
    lower respiratory tract infection
    skin infection
    skin lesions
    skin disorder
    gastrointestinal tract disorder
    gastrointestinal tract infection

    and they are all mixed up....

    So I would like to classify them into respiratory, skin, gastrointestinal

    Can someone please help me with a formula which says : if a cell contains the part text (resp), or the part text (skin), or the part text (gastro), then return (respiratory, skin, gastrointestinal) otherwise nothing.


    Thanks in advance
    Last edited by Newby2012; 04-07-2012 at 06:49 PM.

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: If a cell contains this part text, or this part text, etc.., return this value, or not

    Welcome to the forum.

    Assuming an entry won't have more than 1 of the criteria you're looking for, how about something like:
    =IF(COUNTIF(B2,"*resp*"),"respiratory","")&IF(COUNTIF(B2,"*skin*"),"skin","")&IF(COUNTIF(B2,"*gastro*"),"gastrointestinal","")

    where B2 is the first cell to be searched. Drag down as far as necessary.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,110

    Re: If a cell contains this part text, or this part text, etc.., return this value, or not

    there is probably a more eligant way, but try....

    =IF(ISERROR(FIND($G$3,A3,1)),"",$G$3)&IF(ISERROR(FIND($G$4,A3,1)),"",$G$4)&IF(ISERROR(FIND($G$5,A3,1)),"",$G$5)

    with
    resp = G3
    skin =G4
    gastro = G5

    and your data in col A
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-06-2012
    Location
    Egypt
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: If a cell contains this part text, or this part text, etc.., return this value, or not

    Thanks very much for the fast reply.

    It worked well but the data is getting trickier.

    Here I have the unique values of the data:


    ABOMASAL DISORDER
    CYSTIC OVARIAN DISEASE
    DYSTOCIA, LARGE FOETUS
    DYSTOCIA, POSTURE
    DYSTOCIA, SIMPLE ASSIST
    DYSTOCIA, TWIN
    FATTY LIVER SYNDROME
    GASTROINTESTINAL TRACT DISORDER, LOWER
    GASTROINTESTINAL TRACT DISORDER, UPPER
    GRASS TETANY
    HEPATIC DISORDER
    KETOSIS
    LAMENESS, FOOT
    LAMENESS, LEG
    LAMENESS, MUSCULOSKELETAL
    MASTITIS
    METRITIS
    MILK FEVER
    OBSTETRIC PARALYSIS
    OTHER MISCELLANEOUS DISORDER
    REPRODUCTIVE TRACT TRAUMA
    RESPIRATORY TRACT DISORDER
    RETAINED FOETAL MEMBRANES
    TEAT DISORDER
    UDDER DISORDER
    UNDIAGNOSED ILLNESS
    UTERINE PROLAPSE

    Now I would like to classify them. So it is just a small twist of your formula that I cannot figure out. I want to look for more than one part text in a cell then return a value. For example, if a cell contains (uter or retain or cyst or milk) then return reproductive & if a cell contains (abom or fatty or liver or keto) then return metabolic and so on.
    Last edited by Newby2012; 04-07-2012 at 08:46 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: If a cell contains this part text, or this part text, etc.., return this value, or not

    Hi
    Just modified the formula for you:
    =IF(COUNTIF(A2,"*resp*"),"respiratory","")&IF(COUNTIF(A2,"*skin*"),"skin","")&IF(COUNTIF(A2,"*gastro*"),"gastrointestinal","")&IF(OR(COUNTIF(A2,"*uter*"),COUNTIF(A2,"*retain*"),COUNTIF(A2,"*cyst*"),COUNTIF(A2,"*milk*")),"Reproductive","")&IF(OR(COUNTIF(A2,"*abom*"),COUNTIF(A2,"*fatty*"),COUNTIF(A2,"*liver*"),COUNTIF(A2,"*keto*")),"Metabolic","")
    Regards...

  6. #6
    Registered User
    Join Date
    04-06-2012
    Location
    Egypt
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: If a cell contains this part text, or this part text, etc.., return this value, or not

    Solved!!!

    Many thanks for everyone took the time and effort to reply to this thread.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1