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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,C ONCATENATE (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 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
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.
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.
ASKER