Re: Using wildcard characters in an IF statement

[Norman Harker]

Hi Carl!

Try:

=IF(ISERROR(SEARCH("?hat",A10)),"Error","OK")

You need to use the SEARCH function. If ?hat appears in the string
searched the SEARCH function will return its position. If the string
?hat doesn't appear in the string searched it returns the dreaded
#VALUE!. So we test for the error.

You can always pre-test conditions in an IF function because they must
always return TRUE or FALSE.

=A10="?hat"
returns TRUE only if A10 has the string "?hat". Any other string will
return false.

=SEARCH("?hat",A10)
Returns the position if the string ?hat is found but returns #VALUE!
if it isn't.

=ISERROR(SEARCH("?hat",A10))
Returns TRUE if the SEARCH function doesn't find the string.

Same logic and approach should work for the * wildcard.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Thursday Public Holidays: None (which must be a reason to celebrate).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[vanbergenmichael]

Wildcard Characters in IF Statements

This is really useful, however I am trying to nest this feature and have been unable to get it to work. Can anybody please help with this. I was attempting it by doing the following:

=IF(SEARCH("*hat*",F4),"Yes",IF(SEARCH("*his*",F4),"Good","NO"))

In words I am trying to acheive:

If *hat* appears in cell F4 then "Yes" otherwise if *his* appears in cell F4 then "Good" otherwise "No"

Why does this not work?
Please help!! Many Thanks in advance

Michael Van Bergen

 

[amithkumar]

pls help us in sloving this isssue its very imp for me ........pls help me i dont know y its giving error
=IF(SEARCH("*hat*",F4),"Yes",IF(SEARCH("*his*",F4),"Good","NO"))