Can the "*" be used in an IF as well as in the SEARCH function?

[Eloise T]

The following working formula checks for the word “Diagnosis” or “Diagnotic” regarding service calls that I’m charged to keep track of.

=IFERROR(SEARCH("*Diag*",J7)>0,0)

Anything that appears in Column J that matches *DIAG* (Note: usage of the * wildcards) is picked up and recorded as Diagnosis.



I wanted to do the same thing with the following formula for the word “Cancel.”

The formula works (only for exactly what is between the " ") if I remove the “*” appearing at the beginning and end of the word Cancel, but not with the embedded "*"s.

The purpose was to catch different spellings, e.g. Cancel, Canceled, Cancelled, etc.

Does the “*” only work for the SEARCH function, or is there a way to make the following work?

=IF(AND(N7>41,K7="*Cancel*"),"True","False")

...other than:
=IF(AND(N7>41,K7="Cancel",K7="Canceled",K7="Cancelled"),"True","False")

 

[NARAYANK991]

Hi ,

You are right that the wildcard character will not work with an IF.

However , the use of the wildcard character in a SEARCH function appears redundant to me , since the SEARCH function returns the position where the looked for sub-string is found within the main string.

For example , a formula such as :

=SEARCH("search","Search here")

will return 1 ; a formula such as :

=SEARCH("search","Do not search here")

will return 8.

It is redundant to use the wildcard character in the second case just because the looked for sub-string search occurs in the middle of the main string Do not search here.

Narayan

 

[NARAYANK991]

Hi ,

The formula you have posted at the end of your post is wrong. What you should be using is an OR function within the AND function , since K7 cannot be Cancel , Canceled and Cancelled at the same time.

Since you are checking for this sub-string at the left of the cell contents , a shorter formula would be :

=IF(AND(N7>41,LEFT(K7,LEN("Cancel"))="Cancel"),"True","False")

Narayan

 

[Eloise T]

Thank you for your insight.
So I can do it this way:

=IF(AND(N7>41,SEARCH("*Cancel*",K7)),"True","False")

 

[Eloise T]

P.S. I found this:
http://exceluser.com/excel_help/functions/guide-to-excel-wildcards.htm

 

[Eloise T]

Hi ,

The formula you have posted at the end of your post is wrong. What you should be using is an OR function within the AND function , since K7 cannot be Cancel , Canceled and Cancelled at the same time. You're absolutely correct. I missed that minor detail.

Since you are checking for this sub-string at the left of the cell contents , a shorter formula would be :

=IF(AND(N7>41,LEFT(K7,LEN("Cancel"))="Cancel"),"True","False")

Narayan

 

[NARAYANK991]

Hi ,

There is some misunderstanding here.

The wildcard character can certainly be used with the SEARCH function , but in this application , it is unnecessary ; the following two formulae will work identically :

=IF(AND(N7>41,ISNUMBER(SEARCH("*Cancel*",K7))),"True","False")

=IF(AND(N7>41,ISNUMBER(SEARCH("Cancel",K7))),"True","False")

Narayan

 

[Eloise T]

Good to know! Thank you!

One question: Is ISNUMBER really necessary since I'm searching for Alpha characters?

 

[NARAYANK991]

Hi ,

The SEARCH function returns two types of results :

1. If the searched for sub-string is found within the main string , it returns an integer which is the position in the main string where the sub-string is found.

2. If the main string does not contain the sub-string which is searched for , it returns the #VALUE! error value.

Thus , checking for whether the SEARCH function returns a number greater than 0 will fail when the sub-string is not found within the main string.

The ISNUMBER function will return TRUE if the return value of the SEARCH function is an integer signifying that the sub-string was found , and will return FALSE when the return value of the SEARCH function is the #VALUE! error value signifying that the sub-string was not found.

You can use the ISERR or ISERROR functions also.

Narayan

 

[Shteven]

Good to know! Thank you!

One question: Is ISNUMBER really necessary since I'm searching for Alpha characters?

Yes. The SEARCH function returns the starting character position of the string, which is a number. If SEARCH finds the string, ISNUMBER is TRUE. If it does not find the string, it returns an error, and ISNUMBER becomes FALSE.

Edit: Narayan beat me to it.

 

[deciog]

Eloise AT

Look if this formula helps you

=HLOOKUP("*Diag*",J7,1,0)

Decio

 

[Eloise T]

Thanks to all for your helpful replies, NARAYANK991, Shteven, and deciog!

 

[Khalid NGO]

Hi,

2 more with COUNTIF/s:

=AND(N7>41,COUNTIF(K7,"*cancel*"))

=NOT(1-SIGN(COUNTIFS(K7,"*cancel*",N7,">41")))

Regards,

 

[bosco_yip]

To check a cell contents the word "cancel" and another cell value >41.

Try to include the above posted formulae, adding some and making a collection.

Data in Column A and B, formulae are (with file attached) :

1] =AND(LEFT(A1,6)="cancel",B1>41)......Narayan

2] =AND(ISNUMBER(MATCH("*cancel*",A1,0)),B1>41)

3] =AND(ISNUMBER(SEARCH("cancel",A1)),B1>41)......Narayan

4] =AND(COUNT(SEARCH("cancel",A1)),B1>41)

5] =AND(ISNUMBER(LOOKUP(1,-SEARCH("cancel",A1))),B1>41)

6] =AND(ISTEXT(HLOOKUP("*cancel*",A1,1,0)),B1>41)......Decio

7] =AND(ISTEXT(VLOOKUP("*cancel*",A1,1,0)),B1>41)

8] =AND(COUNTIF(A1,"*cancel*"),B1>41)......Khalid

9] =NOT(1-SIGN(COUNTIFS(A1,"*cancel*",B1,">41")))......Khalid

10] =COUNTIFS(A1,"*cancel*",B1,">41")>0

Regards
Bosco

 

[Eloise T]

Hi,

2 more with COUNTIF/s:

=AND(N7>41,COUNTIF(K7,"*cancel*"))

=NOT(1-SIGN(COUNTIFS(K7,"*cancel*",N7,">41")))

Regards,

THANK YOU!

 

[Eloise T]

To check a cell contents the word "cancel" and another cell value >41.

Try to include the above posted formulae, adding some and making a collection.

Data in Column A and B, formulae are (with file attached) :

1] =AND(LEFT(A1,6)="cancel",B1>41)......Narayan

2] =AND(ISNUMBER(MATCH("*cancel*",A1,0)),B1>41)

3] =AND(ISNUMBER(SEARCH("cancel",A1)),B1>41)......Narayan

4] =AND(COUNT(SEARCH("cancel",A1)),B1>41)

5] =AND(ISNUMBER(LOOKUP(1,-SEARCH("cancel",A1))),B1>41)

6] =AND(ISTEXT(HLOOKUP("*cancel*",A1,1,0)),B1>41)......Decio

7] =AND(ISTEXT(VLOOKUP("*cancel*",A1,1,0)),B1>41)

8] =AND(COUNTIF(A1,"*cancel*"),B1>41)......Khalid

9] =NOT(1-SIGN(COUNTIFS(A1,"*cancel*",B1,">41")))......Khalid

10] =COUNTIFS(A1,"*cancel*",B1,">41")>0

Regards
Bosco

VERY NICE!