How do I include * in an IF statement? For example, how do I write
=IF(A1="*abc*","in there", "not there") when "abc" is contained anywhere in
the string in cell A1?

TIA

Try one of these:

=IF(ISNUMBER(SEARCH("abc",A1)),"in there","not there")
or
=IF(COUNTIF(A1,"*abc*"),"in there","not there")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Harley" wrote:

How do I include * in an IF statement? For example, how do I write
=IF(A1="*abc*","in there", "not there") when "abc" is contained anywhere in
the string in cell A1?

TIA

=if(isnumber(search("abc",a1)),"yep","nope")

=search() is case sensitive.
=find() is not.

alternatively:

=if(countif(a1,"*abc*")0, "yep", "nope")

(not case sensitive)

Harley wrote:

How do I include * in an IF statement? For example, how do I write
=IF(A1="*abc*","in there", "not there") when "abc" is contained anywhere in
the string in cell A1?

TIA

--

Dave Peterson

Hi Harley,

Try =IF(ISERROR(FIND("abc",A1,1)),"NOT THERE","THERE")

Ewan

"Harley" wrote:

How do I include * in an IF statement? For example, how do I write
=IF(A1="*abc*","in there", "not there") when "abc" is contained anywhere in
the string in cell A1?

TIA

=find() IS case sensitive
=search() IS NOT.

(and =countif() is not case sensitive)

Dohhh.

Dave Peterson wrote:

=if(isnumber(search("abc",a1)),"yep","nope")

=search() is case sensitive.
=find() is not.

alternatively:

=if(countif(a1,"*abc*")0, "yep", "nope")

(not case sensitive)

Harley wrote:

How do I include * in an IF statement? For example, how do I write
=IF(A1="*abc*","in there", "not there") when "abc" is contained anywhere in
the string in cell A1?

TIA

--

Dave Peterson

--

Dave Peterson

Thank you Ron and Ewan, all the formulas worked.

"ewan7279" wrote:

Hi Harley,

Try =IF(ISERROR(FIND("abc",A1,1)),"NOT THERE","THERE")

Ewan

"Harley" wrote:

How do I include * in an IF statement? For example, how do I write
=IF(A1="*abc*","in there", "not there") when "abc" is contained anywhere in
the string in cell A1?

TIA