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

1. ## 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.

2. ## 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. ## 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

4. ## 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.

5. ## 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. ## 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

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

#### 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