If statement to compare for wildcard value in another cell.

[Karen53]

Hello,

I need to compare for a value contained within another cell. I need to test
for a portion within the cell so I'm using wildcards. I have tried various
combinations but it's not working. What am I missing?

Here is one I've tried.

=IF(n2=*g2*,1,0)

Thanks for your help,

 

[Biff]

Hi!

Try this:

=ISNUMBER(SEARCH(G2,N2))*1

Biff

 

[Karen53]

thanks Biff.

the data is text and what I'm testing for could have text before it and
after it. I used your suggestion but tried substituting ISTEXT but it did
not work. The statement should have been true and returned 1 but it returns
0.

Any ideas?

thanks

 

[Biff]

Hi!

I used your suggestion but tried substituting ISTEXT but it did
not work

Do you mean you tried this:

=ISTEXT(SEARCH(G2,N2))*1

If so, ISNUMBER/ISTEXT has nothing to do with the data type of the SEARCH
value.

You use ISNUMBER because if the SEARCH string is found, SEARCH returns the
number position of the string where it was found then that value is passed
to ISNUMBER which will evaluate to TRUE and finally, *1 will convert the
boolean TRUE to numeric 1.

So, using ISTEXT together with SEARCH in this manner will *ALWAYS* evaluate
to FALSE.

Try it as I suggested. If that doesn't work then post an example (or two).

Biff