Link to home
Start Free TrialLog in
Avatar of toddweaver
toddweaver

asked on

If statements in excel

I want to concatenate two text cells, but only "if" cell X does not "contain" cell Y.  Cell X is the text string with spaces between words - no puntuation.  No spaces in text cell Y.
Example:
text cell X = cat dog rain
text cell Y = cat
Concatenation yields result:  cat dog rain  (X contains Y)
Example 2:
text cell X = fish blue thunder
text cell Y = cat
Concatenation yields result:  fish blue thunder cat (X does not contain Y
I guess I am looking for an "If contains" statement or function.  Maybe there are a few functions to perform.
Avatar of toddweaver
toddweaver

ASKER

Edited text of question
ASKER CERTIFIED SOLUTION
Avatar of Pandora
Pandora
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excel changed my formula from what I typed to what you see below.
It seems to have worked.  I just need to tinker a bit to get my desired result, for instance I want it to print B1 concatenated with A1 especially if it does not contain A1.  So, I think I am close.  It could be that my brain is off at this time in the evening.  Where can I find formulas like the one you gave me?
=CONCATENATE(IF(FIND(A1,B1,1)>0,B1,CONCATENATE(A1," ",B1)))
cat      mouse cat      mouse cat
cat      mouse cat      mouse cat
cat      mouse      #VALUE!
dog      dog      dog
dog      dog      dog
dog      dog      dog
mouse      mouse      mouse
mouse      mouse      mouse
mouse      blue      #VALUE!

It should not need the additional concatenate statement (it is simply adding itself to nothing, which is why it still works); the best place to find formulae is usind the f(x) toolbar button next to the sigma button; the functions are listed in groups and the ones you are interested in are:
logical - if statements
text - find, concatenate
the expression builder will tell you what arguments go where, and what they do.  select a blank cell and then click {fx} and have a play - build the statements one at a time from the inside out (this is called 'nesting') and when you're happy each stage works, build the next one inside it - good luck & happy hunting!
P.