if statements using contains

M

Merge

I have a string of data in a cell and I want to say
If(a2 contains "admin","true,"false")
I don't know how to say "contains" in this format, I may want to expand of
the logic and say if(a2 contains "admin" and (contains
"conversion"),1.25,false))

Cell a2 contains; plant one administrative assit(conversion)
 
M

Mike H

Hi,

=NOT(ISERROR(SEARCH("Admin",A2)))

and this

IF(NOT(ISERROR(AND(SEARCH("Admin",A2),SEARCH("Conversion",A2)))),1.25,"False")

Mike

Mike
 
B

Bernard Liengme

Q1: =IF(ISNUMBER(FIND("admin",A1)),TRUE,FALSE)
Note I am using Boolean values TRUE/FALSE but you could use text
=IF(ISNUMBER(FIND("admin",A1)),"True", "False")
But we do not need IF when only TRUE/FALSE is required,
we can use just =ISNUMBER(FIND("admin",A1))

Q2:
=IF(AND(ISNUMBER(FIND("admin",A1)),ISNUMBER(FIND("conversion",A1))),1.25,FALSE)
but again we can simplify with
=IF(AND(ISNUMBER(FIND("admin",A1)),ISNUMBER(FIND("conversion",A1))),1.25)
best wishes
 
B

bpeltzer

=IF(ISNUMBER(FIND("admin",A2)),TRUE,FALSE)
(If you really only need the true or false result, you could skip the IF and
just use =ISNUMBER(FIND("admin",a2)); the result of the ISNUMBER function
will be either TRUE or FALSE, making the IF redundant.)

To require two strings to match within a single cell:
=IF(AND(ISNUMBER(FIND(string1,a2)),ISNUMBER(FIND(string2,a2))),true_value,false_value)

Be aware that the FIND is case-sensitive. You could get around that by
using UPPER to convert the input and comparing to an uppercase string:
=ISNUMBER(FIND("ADMIN",UPPER(a2))
 
Top