CODEWITHSUNDEEP
excelif-statement
K.Honda
K.Honda

Reputation: 3106

If cell contains text wildcard - excel

I'm trying to use an IF statement to find a string of text with a wildcard in a particular cell. E.g. 

=IF(OR(K3="Fixed Rate*", K3="Mobile Rate*"), "true", "false")

I have also tried something like this:

=ISNUMBER(SEARCH("Fixed Rate*",K3))

But everything seems to end up in "false". Ideally, I would like to find if a particular cell has either wildcard of Fixed Rate... or wildcard of Mobile Rate...

Does anyone know why and have a solution?

Thanks in advance!

Upvotes: 0

Views: 7094

Answers (3)

JvdV
JvdV

Reputation: 75840

Credits to @Olly. You could also do it like:

=OR(NOT(ISERROR(MATCH("*Fixed Rate*",K3,0))),NOT(ISERROR(MATCH("*Mobile Rate*",K3,0))))

Upvotes: 1

Ralara
Ralara

Reputation: 569

Combining your two ideas produces the right result.

=IF(OR(ISNUMBER(SEARCH("Fixed Rate*",K3)),ISNUMBER(SEARCH("Mobile Rate*",K3))), "true", "false")

You need to use the SEARCH function to allow wildcards, then the OR function to check for the presence of either string.

Upvotes: 1

Olly
Olly

Reputation: 7891

You're nearly there. Try this:

=OR(NOT(ISERROR(SEARCH("fixed rate",K3))),NOT(ISERROR(SEARCH("mobile rate",K3))))

Upvotes: 2

Related Questions