Is there an "is like" in Excel? ---- SOLVED -----

[invisigirl]

For some reason, my brain has passed away. I need a formula that returns one of two answers (that I supply) based on whether words in the MIDDLE of a cell are two certain words. I could do this with conditional formatting if there was an "is like" match, instead of just numeric possibilities like =<> etc.

So, in English, what the formula should SAY is this: "If cell B10 contains the words 'limited coverage,' display the text 'NOTE LIMITED COVERAGE!' If it doesn't, display nothing."

This would be so dang easy if those words were at the BEGINNING of the cell. Oh well. What has happened to my brain? It keeps thinking of things I could do in Access or Showcase, and I'm obviously overlooking something easy.

Help!
This message was edited by invisigirl on 2002-10-22 20:02

 

[Juan Pablo González]

You can use either FIND or SEARCH worksheet functions (Which one depends if you need case sensitive comparison or not)

 

[invisigirl]

Well, I could, but my users can't. It has to be something that displays just because they hit enter. The spreadsheet is already set up to give them other results just by entering a number in one cell...this is just an additional message I want to add.

 

[zacemmel]

=if(iserror(find("limited coverage", lower(a1),1)),0,1)

This will return 1 if "limited coverage" was found anywhere in cell A1.

Hope this helps.

Questions?

http://www.excelquestions.com

 

[Juan Pablo González]

Mhmm, lost me a bit here:

=IF(ISNUMBER(FIND("mytext",A1)),"I found it","")

would work just like you describe...

 

[invisigirl]

Juan, thanks!
There aren't any numbers in the cell I'm referencing. It begins with a word or words, but the words I'm looking for will be somewhere in the middle. What would I substitute for your suggestion in that case?

 

[Juan Pablo González]

Nothing, the FIND function (As well as the SEARCH) look for the first parameter ANYWHERE in the second parameter, if it finds it, it returns the position that it found it.

 

[invisigirl]

Thanks so much! I had just taken out the ISNUMBER part, which left me with an error message on false results. Put it back in and it works fine.

Guess that's why I have so much trouble in Excel - my brain keeps rejecting the terminology.

Thanks again, Juan. Happy day!

 

[Ekim]

These work with your search string in D1.

1. Case sensitive:

=IF(FIND($D$1,A1)),"I found it","")

2. Non-case sensitive:

=IF(SEARCH($D$1,A1),"I found it","")


Regard,

Mike

 

[Aladin Akyurek]

On 2002-10-23 04:53, Ekim wrote:
These work with your search string in D1.

1. Case sensitive:

=IF(FIND($D$1,A1)),"I found it","")

2. Non-case sensitive:

=IF(SEARCH($D$1,A1),"I found it","")

Mike,

Without an ISNUMBER test, the above formulas will error out, so you will never get "".

Functions like SEARCH, FIND, and MATCH return a number when they succeed, otherwise an error value. That's why it is often more appropriate to use an ISNUMBER test instead of an ISERROR, ISERR, or ISNA test with these functions.

Aladin