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

 

[Ekim]

Aladin,

Good point.

Kind regards,

Mike

 

[rpatric]

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

Another close "like" formula is to utilize the "*" in count and sum formulas. For example, if column B has text strings(sentences) and I want to count the number of cells that contain a specific word within the sentences, I could use:

1.)
=countif(B2:B34,"*","Postcard","*")
This allows me to search for the word "Postcard" within the cells even if the word lies within a sentence in the cell.

2.) =sumif(B2:B34,"*","Postcard","*",C2:C34)
This allows me to sum the cells in column C that correspond to cells containing "Postcard" in column B.

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

 

[Aladin Akyurek]

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

Are you in asking or teaching mode?

Another close "like" formula is to utilize the "*" in count and sum formulas. For example, if column B has text strings(sentences) and I want to count the number of cells that contain a specific word within the sentences, I could use:

1.)
=countif(B2:B34,"*","Postcard","*")
This allows me to search for the word "Postcard" within the cells even if the word lies within a sentence in the cell.

That should be rather:

=COUNTIF(B2:B34,"*"&"PostCard"&"*")

If E2 would house Postcard, the formula would become:

=COUNTIF(B2:B34,"*"&E2&"*")

2.) =sumif(B2:B34,"*","Postcard","*",C2:C34)
This allows me to sum the cells in column C that correspond to cells containing "Postcard" in column B.

See above for the correct form.

By the way:

*X*

means: Contains X;

*X

means: Ends with X;

X*

means: Begins with X.

Also, we can have ? as wildcard, which stands for 1 char. And combination of ? and *. Try to experiment also with ? and the combinations.