Can you use a wildcard search in a if function (excel)? 1

[punchedin]

What I am trying to do is have a formula that will look in A1 and if it see the word "sign" to put yes in B1. Is this possible?

Thanks

 

[Adnane]

you can use B1=if(find("sign",A1),"eyes","")

Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[mscallisto]

Yes you can, try looking in excel help for the Left, Search and Find worksheet functions for examples.

Can you provide a better example of what you need to do?

 

[punchedin]

I have a description column and I would like to search that column based on a few key words. For example:
A1: Can you read the sign.
B1: This is where is would like the word sign.

A2: Do you see the bus.
B2: Key word = bus.


To look at my results I would use a filter on the key word column.

Does this help.

 

[punchedin]

Adnane

=if(find("sign",A1),"eyes","")

That appears to work.

is there a way to not have it return the #VALUE! if the find is empty?

 

[mscallisto]

=IF(ISERROR(SEARCH("hat",A1,3)),"no hat","yes hat")

 

[mscallisto]

Correction

=IF(ISERROR(SEARCH("hat",A1,1)),"no hat","yes hat")

 

[Adnane]

=if(isna(if(find("sign",A1),"eyes","")),"",if(find("sign",A1),"eyes",""))

would work also if you want to control only the #N/A error

Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[Adnane]

i apologize, i didn't read very carefully what you were asking for. ISERROR will work indeed.

Sorry

Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[mscallisto]

Absolutely no need to apoligize, you made good responses!

 

[punchedin]

Thanks to both of you for all of your help. Much appreciated.

 

[KenWright]

Some examples of syntax using wildcards:-

=IF(COUNTIF(A2:A2,"?abc"),"yes","no")

=IF(COUNTIF(A2:A2,"*abc"),"yes","no")

=IF(COUNTIF(A2:A2,"*abc*"),"yes","no")

=IF(ISNUMBER(FIND("abc",A2)),"yes","no")

=IF(ISNUMBER(FIND("abc",A2)),"yes","no")

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[mscallisto]

Very usefull addition Ken, I'm sure punchedin will benefit although he really didn't necessarily need a wildcard in this case.

 

[KenWright]

True, and to be honest I prefer your 'Search' to 'Find' anyway, as Find is Case sensitive, whereas Search isn't, which can throw up unexpected results (or the opposite even, in not throwing up expected results) if not known.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------