If Statements - how to do is like '%value%'

  • Thread starter Amanda Guenthner
  • Start date
A

Amanda Guenthner

Hi-

I am trying to flag a record as being either “CCO†or “CCTâ€, where any
record with either of the following would be flagged as CCT (else would be
CCO):
1. Domain value is at least one of the following: "Customer Contact
Transformation (CCT/ACCSâ€, “Customer Contact Transformation","Customer
Contact Transformation (Scale Play)"
OR
2. Campaign = "Customer Contact Transformation (CCT)"

I had used the following if statement:
=IF(N10&O10="","",IF(OR(N10="Customer Contact Transformation
(CCT/ACCS)",N10="Customer Contact Transformation",N10="Customer Contact
Transformation (Scale Play)",O10="Customer Contact Transformation
(CCT)"),("CCT"),("CCO")))

The problem with this, however, is that the Domain field can have multiple
values (i.e. can have one of the CCT related values in #1 plus another
unrelated value). This statement is only looking for exact matches; hence I
am not flagging all those that I need to because I want it to = CCT if any
one of the Domain values contains CCT or the Campaign value contains CCT.

Is there a way to structure IF statements to look for records that contain
certain text (i.e. like in SQL how you can do  is like ‘%CCT%’)? Perhaps I
need to do something more complex than an IF statement…not sure what this
would be though.

I would really appreciate any help with this. Thanks in advance.

Amanda
 
P

PC

How about something along the lines of

IF(OR(ISERROR(FIND("CCT",A1))=FALSE,ISERROR(FIND("CCO",A1))=FALSE),"somethin
g","something else")

You may need to fiddle with that syntaxt, but the idea is to have the
formula search the string for the three critical values.

PC
 
R

Ron Rosenfeld

Is there a way to structure IF statements to look for records that contain
certain text (i.e. like in SQL how you can do ? is like ‘%CCT%’)?
Click to expand...

Several methods:

=COUNTIF(range_to_search,"*CCT*") will return 0 if CCT is not present. (Note
the "*" wild card characters).

=FIND("CCT",G1) will return #VALUE! if CCT not present, otherwise it will
return a positive integer. (Note NO wild card characters allowed).

=SEARCH("CCT",G1) will do the same as FIND, but is case-insensitive.


--ron
 
R

RagDyeR

Don't know if I follow all your conditions, but try this concept:

=IF(N10&O10="","",IF(OR(COUNTIF(N10,"Customer Contact
Transformation*")>0,O10="Customer Contact Transformation
(CCT)"),"CCT","CCO"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message Hi-

I am trying to flag a record as being either "CCO" or "CCT", where any
record with either of the following would be flagged as CCT (else would be
CCO):
1. Domain value is at least one of the following: "Customer Contact
Transformation (CCT/ACCS", "Customer Contact Transformation","Customer
Contact Transformation (Scale Play)"
OR
2. Campaign = "Customer Contact Transformation (CCT)"

I had used the following if statement:
=IF(N10&O10="","",IF(OR(N10="Customer Contact Transformation
(CCT/ACCS)",N10="Customer Contact Transformation",N10="Customer Contact
Transformation (Scale Play)",O10="Customer Contact Transformation
(CCT)"),("CCT"),("CCO")))

The problem with this, however, is that the Domain field can have multiple
values (i.e. can have one of the CCT related values in #1 plus another
unrelated value). This statement is only looking for exact matches; hence I
am not flagging all those that I need to because I want it to = CCT if any
one of the Domain values contains CCT or the Campaign value contains CCT.

Is there a way to structure IF statements to look for records that contain
certain text (i.e. like in SQL how you can do ? is like '%CCT%')? Perhaps I
need to do something more complex than an IF statement.not sure what this
would be though.

I would really appreciate any help with this. Thanks in advance.

Amanda
 
A

Amanda Guenthner

Thanks - that did the trick. Although I did need to create another column
that had the =COUNTIF(range_to_search,"*CCT*") formula. I then referenced
this new column with a more simple if statement (i.e.
IF(A1>0,("CCT"),("CCO"))).

Is there a way to combine the logic you provided to do the wildcard search
for the string embedded in my initial IF statement? No worries if this is
really complicated.

Thanks again, Amanda
 
R

Ron Rosenfeld

Is there a way to combine the logic you provided to do the wildcard search
for the string embedded in my initial IF statement? No worries if this is
really complicated.
Click to expand...

You can combine things using OR constructs. (Adding things the way I do below
is the equivalent of OR).

However, since I don't know all of the possibilities, you may have to modify my
suggestion. It should give you some ideas, though:

=IF(N10&O10="","",
IF(COUNTIF(N10,"*CCT*")+
COUNTIF(N10,"*Customer Contact Transformation*")+
COUNTIF(O10,Customer Contact Transformation),
"CCT","CCO"))



--ron
 
Top